﻿<?php
//tuannguyen2042 added 9/6/2013
App::import('Vendor', 'simpledom/simple_html_dom');
App::import('HttpSocket', 'Network/Http');

class InitsController extends AppController {

  var $name = 'Inits';
  var $uses = array('Category', 'Location', 'Listing', 'Review');
  var $components = array('Yell', 'Yahoo', 'RequestHandler');
  // 86400 = 24 chasa | 864000 = 10 dena | 2592000 = 30 dena | 31536000 = 365 dena | 157680000 = 5 godini
  var $cacheAction = array(
    'parse_request' => '157680000'
  );

  /**
   * Category Model
   *
   * @var Category
   */
  var $Category;

  /**
   * Location Model
   *
   * @var Location
   */
  var $Location;

  /**
   * Listing Model
   *
   * @var Listing
   */
  var $Listing;

  /**
   * Tyk zadavame spisaka na action-ite s publichen dostyp (action-ite bez authorizaciq)
   */
  function __initPublicActions() {
    $this->Auth->allow(array(
      'init_db',
      'parse_request',
      'ajaxGetChildCats',
      'ajaxFilterListings'
    ));
  }

  function beforeFilter() {
    parent::beforeFilter();
    $this->__initPublicActions();
  }

  function init_db() {
    $this->loadModel('Group');

    $group = & $this->Group;

    //------------- root [id=1]
    $group->id = 1;

    $this->Acl->allow($group, 'controllers');
    //------------/ root [id=1]
    //------------- user [id=2]
    $group->id = 2;

    $this->Acl->allow($group, 'controllers/Inits/parse_request');
    //------------/ user [id=2]

    echo "all done";
    exit;
  }

  function parse_request(){
	$this->layout= 'newdefault';
    //tuannguyen2042 hide 09/04/2013
    ini_set('max_execution_time', 3600 * 5);
    /*
    $si = preg_split('#/#is', $this->params['url']['url'], -1, PREG_SPLIT_NO_EMPTY);

    $cats = array();
    $locs = array();
    foreach ($si as $s) {
      if ( ( $x = $this->Category->find(array('url_title' => $s)) ) ) {
        $cats[] = $x['Category']['id'];
      } elseif ( ( $x = $this->Location->find(array('url_title' => $s)) ) ) {
        $locs[] = $x['Location']['id'];
      }
    }

    $cat_id = count($cats) > 0 ? $cats[count($cats) - 1] : 0;
    $loc_id = count($locs) > 0 ? $locs[count($locs) - 1] : 0;

    $yell = $this->Yell->getResults($cat_id, $loc_id, 5);
    $yahoo = $this->Yahoo->getResults($cat_id, $loc_id, 8);

    $this->set('yell', $yell);
    $this->set('yahoo', $yahoo);
    */

    //tuannguyen2042 added 09/04/2013
    if ($this->params['pass'][0] == 'updatestations') {
      $limit = $this->params['pass'][1];
      if (empty($limit)) {
        $limit = 10;
      }
      $this->updateStations($limit);
      exit();
    }
    
    if ($this->params['pass'][0] == 'updatelocationlatlng') {
      $limit = $this->params['pass'][1];
      if (empty($limit)) {
        $limit = 10;
      }
      $this->updateLocationLatlng($limit);
      exit();
    }
    
    if ($this->params['pass'][0] == 'updatestreetview') {
      $limit = $this->params['pass'][1];
      if (empty($limit)) {
        $limit = 10;
      }
      $this->updateStreetView($limit);
      exit();
    }
    
    if ($this->params['pass'][0] == 'updatenearbystreets') {
      $limit = $this->params['pass'][1];
      if (empty($limit)) {
        $limit = 10;
      }
      $this->updateNearbyStreets($limit);
      exit();
    }
    
    if ($this->params['pass'][0] == 'updatenearby') {
      $limit = $this->params['pass'][1];
      if (empty($limit)) {
        $limit = 10; 
      } 
      $this->updateNearby($limit);
      exit();
    }
    
    if ($this->params['pass'][0] == 'yell' && $this->params['pass'][1] == 'cronjob') {
      $limit = $this->params['pass'][2];
      if (empty($limit)) {
        $limit = 100;
      }

      $main_location = $this->params['pass'][3];
      if (empty($main_location)) {
        $main_location = '';
      }

      ini_set('max_execution_time', 3600 * 5);
      $this->runYellCronjob($limit, $main_location);
      exit();
    }

    if ($this->params['pass'][0] == 'yell' && $this->params['pass'][1] == 'filter') {
      $this->runYellDuplicateFilter();
      exit();
    }

    if ($this->params['pass'][0] == 'yell' && $this->params['pass'][1] == 'deactiveall') {
      $this->deactiveall();
      exit();
    }

    if ($this->params['pass'][0] == 'activelistings') {
      $num_listings = 50;
      if (isset($this->params['pass'][1]) && is_numeric($this->params['pass'][1])) {
        $num_listings = $this->params['pass'][1];
      }

      $this->activelistings($num_listings);
      exit();
    }
    //end


    $isFoundListing = false;
    $isFoundLocation = false;
    $isFoundCategoryLocation = false;
    $isFoundCategory = false;

    $theListing = array();
    $theLocation = array();
    $theCategoryLocation = array();
    $theCategory = array();

    $pass = $this->params["pass"];

    if ((!empty($this->params["url"]["url"])) && ($this->__endsWith($this->params["url"]["url"], ".html"))) {
      $theListing = $this->__findListing($this->params["url"]["url"]);

      if (!empty($theListing)) {
        //------ FOUND LISTING
        $isFoundListing = true;
      }
    } 
    elseif (!empty($pass)) {
      if(strpos($pass[1], ' ') !== FALSE){
        $isFoundLocations = true;
        $listings = split(" ", $pass[1]);                
      }

      $theLocation = $this->__isLocationDirFirst($pass);
      if (!empty($theLocation)) {

        //tuannguyen2042 added
        if (count($pass) > 2) {
          unset($pass[0]);
          $pass = array_values($pass);
          $theLocation = $this->__isLocationDirFirst($pass);
        }
        //end

        $theSingleLocation = $this->__findSingleLocation($pass);
        if (!empty($theSingleLocation)) {      //sub-loccation
          //------ FOUND SINGLE LOCATION
          $isFoundLocation = true;
          $theLocation = $theSingleLocation;
        } 
        else {
          $theCategoryLocation = $this->__findCategoryLocation($pass);
          if (!empty($theCategoryLocation)) {
            //------ FOUND CATEGORY LOCATION
            $isFoundCategoryLocation = true;
          }
        }
      } 
      else {
        $theCategory = $this->__findCategory($pass);
        if (!empty($theCategory)) {
          //------ FOUND CATEGORY
          $isFoundCategory = true;
        }
      }
    }

    $data = array();

    $isFound = ($isFoundListing || $isFoundLocation || $isFoundLocations || $isFoundCategoryLocation || $isFoundCategory);
    if ($isFound) {
      if ($isFoundListing) {
        $data = array("LISTING_TYPE", $theListing);
          // GET REVIEW
          $a_Reviews = $this->Review->find('all', array('order' => array('Review.id DESC'), 'conditions' => array('Review.listing_id' => $theListing['Listing']['id'], 'Review.status' => 1)));
          $i_TotalReview = count($a_Reviews);
          $i_AVGRating = 0;
          $i_TotalRating = 0;

          foreach ($a_Reviews as $a_Review) {
            $i_TotalRating = $i_TotalRating + $a_Review['Review']['rate'];
          }
          
          $i_AVGRating = round(($i_TotalRating * 2) / $i_TotalReview) / 2;
          $nearby = split('-', $theListing['Listing']['nearby']);
          $index = 0;
          if ($nearby[0] == '') {
            $nearby = '';
          }
          
          foreach ($nearby as $listing2) {
            $iddistance = split(':', $listing2);
            $listingname = $this->Listing->find('all', array('conditions' => array('Listing.id = ' => $iddistance[0])));
            $out = '<div id="listing-rate" style="vertical-align: middle;padding-bottom: 2px;padding-top: 2px;width: 165px">';
            
            if ((($i_AVGRating * 2) % 2) == 0) {
              $i_MaxEn = $i_AVGRating;
              $i_MaxDis = 5 - $i_MaxEn;
              $b_Half = false;
            } 
            else {
              $i_MaxEn = $i_AVGRating - 0.5;
              $i_MaxDis = 5 - ($i_MaxEn + 1);
              $b_Half = true;
            }
            
            for ($i = 0; $i < $i_MaxEn; $i++) :
              $out = $out . '<img src="' . SITE_WEBROOT . 'img/stars_enabled.png" width="15"/>';
            endfor;
            
            if ($b_Half):
              $out = $out . '<img src="' . SITE_WEBROOT . 'img/stars_half.png" width="15"/>';
            endif;
            
            for ($i = 0; $i < $i_MaxDis; $i++) :
              $out = $out . '<img src="' . SITE_WEBROOT . 'img/stars_disabled.png" width="15"/>';
            endfor;
            
            $out = $out . '<span style="float: right;">&nbsp;&nbsp;' . $i_TotalReview . ' Reviews</span>';
            $out = $out . '<br/>' . $listingname[0]['Listing']['address_1'];
            $out = $out . '<div style="clear: both;"></div>';
            $out = $out . '</div>';

            $out = str_replace('"', "'", $out);

            $link = '<a href="' . SITE_WEBROOT . $listingname[0]["Listing"]["url_title"] . '-l' . $listingname[0]["Listing"]["id"] . '.html">' . htmlspecialchars($listingname[0]["Listing"]["title"]) . '</a>';
            $link = str_replace('"', "'", $link);
            if ($listingname[0]["Listing"]["title"] != '') {
              $val = $iddistance[1];
              $nearbylocation = $nearbylocation . $link . ' ' . $val . ' miles</br>' . $out . '</br>';
            }
            $index++;
          }

          $this->set('i_TotalReview', $i_TotalReview);
          $this->set('i_AVGRating', $i_AVGRating);
          $this->set('a_Reviews', $a_Reviews);
          $this->set('_nearby', $nearbylocation);
        } 
        elseif ($isFoundLocation) {
          $data = array("LOCATION_TYPE", $theLocation);
        }
        elseif ($isFoundLocations) {
          $data = array("LOCATIONS_TYPE", $listings);
        }
        elseif ($isFoundCategoryLocation) {
          $data = array("CATEGORYLOCATION_TYPE", $theCategoryLocation, $theLocation);
          
          // GET REVIEW
          $review = $this->Review->find('all', array('order' => array('Review.id DESC'), 'conditions' => array('Review.category_id' => $theCategoryLocation['Category']['id'], 'Review.status' => 1), 'limit' => 5));
          $this->set('review', $review);
          // END GET REVIEW
        } 
        elseif ($isFoundCategory) {
          $data = array("CATEGORY_TYPE", $theCategory);
          
          // GET REVIEW
          $review = $this->Review->find('all', array('order' => array('Review.id DESC'), 'conditions' => array('Review.category_id' => $theCategory['Category']['id'], 'Review.status' => 1), 'limit' => 5));
          $this->set('review', $review);
          // END GET REVIEW
        } 
        else {
          $data = array();
        }

        $this->__display_request($data);
      } 
      else {
        $this->cakeError('error404');
      }
  }

  function __findListing($url = null) {
        $theListing = array();

        if ($url != null) {
            $this->Listing->recursive = 1;

            $listingURL = rtrim($url, ".html");

            $listingDelimeter = "-l";
            $listingDelimeterPosition = mb_strrpos($listingURL, $listingDelimeter);

            $listingURLTitle = mb_substr($listingURL, 0, $listingDelimeterPosition);
            $listingID = mb_substr($listingURL, ($listingDelimeterPosition + mb_strlen($listingDelimeter)));

            $theListing = $this->Listing->find('first', array(
                'conditions' => array(
                    'Listing.id' => $listingID,
                    'Listing.url_title' => $listingURLTitle
                )
            ));

            if (!empty($theListing)) {
                $theListing["CATEGORY_PATH"] = $this->Category->getpath($theListing["Category"]["id"]);

                $theListing["CATEGORY_PATH_URL"] = '';
                $curegoryPathURL = '';
                for ($i = 0, $curegoryPathSize = count($theListing["CATEGORY_PATH"]); $i < $curegoryPathSize; $i++) {
                    if ($i != 0) {
                        $curegoryPathURL .= '/';
                    } else {
                        $curegoryPathURL .= '';
                    }

                    $curegoryPathURL .= $theListing["CATEGORY_PATH"][$i]["Category"]["url_title"];

                    $theListing["CATEGORY_PATH_URL"][] = $curegoryPathURL;
                }
            }
        }

        return $theListing;
    }

    function __isLocationDirFirst($pass = array()) {
        $urlLocation = $pass;
        $theLocation = array();

        if (!empty($urlLocation)) {
            $this->Location->recursive = -1;

            $theLocation = $this->Location->find('first', array(
                'conditions' => array('url_title' => $urlLocation[0])
            ));
        }

        return $theLocation;
    }

    function __findSingleLocation($pass = array()) {
        $urlLocation = $pass;
        $theLocation = array();
        $cnt = count($urlLocation);

        if (!empty($urlLocation)) {
            $this->Location->recursive = -1;
            if ($cnt == 1) {
                $theLocation = $this->Location->find('first', array('conditions' => array('url_title' => $urlLocation[0])));
            } elseif ($cnt == 2) {
                $theLocation = $this->Location->find('first', array('conditions' => array('url_title' => $urlLocation[1])));
            }
        }
        return $theLocation;
    }

    function __findCategoryLocation($pass = array()) {
        $urlCategories = $pass;
        $theCategory = array();

        if ((!empty($urlCategories)) && (count($urlCategories) > 1)) {
            // remove location (the first dir)
            unset($urlCategories[0]);
            $urlCategories = array_values($urlCategories);

            $this->Category->recursive = -1;

            $urlCategoriesCount = count($urlCategories);

            // otkriti kategorii sprqmo poslednata kategoriq v $urlCategories
            $foundCategoriesByURLTitle = $this->Category->find('all', array(
                'conditions' => array('url_title' => $urlCategories[$urlCategoriesCount - 1])
            ));

            // tyrsene na pravilnata kategoriq sprqmo path-a na url-to i path-a na dadenata kategoriq
            if ($urlCategoriesCount > 1) {
                for ($i = 0, $categoriesCount = count($foundCategoriesByURLTitle); $i < $categoriesCount; $i++) {
                    $currentCategotyPath = "";
                    $currentCategotyPathElements = $this->Category->getpath($foundCategoriesByURLTitle[$i]["Category"]["id"]);
                    for ($j = 0, $currentCategotyPathElementsSize = count($currentCategotyPathElements); $j < $currentCategotyPathElementsSize; $j++) {
                        $currentCategotyPath .= "/" . $currentCategotyPathElements[$j]["Category"]["url_title"];
                    }

                    if ((trim(mb_substr(trim($this->params["url"]["url"], "/"), mb_strpos(trim($this->params["url"]["url"], "/"), "/")), "/")) == trim($currentCategotyPath, "/")) {
                        $theCategory = $foundCategoriesByURLTitle[$i];
                        break;
                    }
                }
            } else {
                $theCategory = $foundCategoriesByURLTitle[0];
            }

            if (!empty($theCategory)) {
                $theCategory["CATEGORY_PATH"] = $this->Category->getpath($theCategory["Category"]["id"]);

                $theCategory["CATEGORY_PATH_URL"] = '';
                $curegoryPathURL = '';
                for ($i = 0, $curegoryPathSize = count($theCategory["CATEGORY_PATH"]); $i < $curegoryPathSize; $i++) {
                    if ($i != 0) {
                        $curegoryPathURL .= '/';
                    } else {
                        $curegoryPathURL .= '';
                    }

                    $curegoryPathURL .= $theCategory["CATEGORY_PATH"][$i]["Category"]["url_title"];

                    $theCategory["CATEGORY_PATH_URL"][] = $curegoryPathURL;
                }

                $theCategory["CATEGORY_CHILDRENS_DIRECT"] = $this->Category->children($theCategory["Category"]["id"], true);
                $theCategory["CATEGORY_CHILDRENS_ALL"] = $this->Category->children($theCategory["Category"]["id"]);
            }
        }
        return $theCategory;
    }

    function search() {
        if (!empty($this->data)) {
            $searchstr = $this->data['Listings']['search'];
            $searchstr2 = $this->data['Listings']['nearby'];
            $this->set('searchstring', $this->data['Listings']['search']);
            $conditions = array(
                'conditions' => array(
                    'or' => array(
                        "listing.title LIKE" => "%$searchstr%",
                        "listing.description LIKE" => "%$searchstr%",
                        "location.name LIKE" => "%$searchstr%"
                    )
                )
            );
            $this->set('Listings', $this->Listing->find('all', $conditions));
            //$this->Inits->parse_request();
            $asd = 0;
        }
    }

    function __findCategory($pass = array()) {
        $urlCategories = $pass;
        $theCategory = array();

        if (!empty($urlCategories)) {
            $this->Category->recursive = -1;

            $urlCategoriesCount = count($urlCategories);

            // otkriti kategorii sprqmo poslednata kategoriq v $urlCategories
            $foundCategoriesByURLTitle = $this->Category->find('all', array(
                'conditions' => array('url_title' => $urlCategories[$urlCategoriesCount - 1])
            ));

            // tyrsene na pravilnata kategoriq sprqmo path-a na url-to i path-a na dadenata kategoriq
            if ($urlCategoriesCount > 1) {
                for ($i = 0, $categoriesCount = count($foundCategoriesByURLTitle); $i < $categoriesCount; $i++) {
                    $currentCategotyPath = "";
                    $currentCategotyPathElements = $this->Category->getpath($foundCategoriesByURLTitle[$i]["Category"]["id"]);
                    for ($j = 0, $currentCategotyPathElementsSize = count($currentCategotyPathElements); $j < $currentCategotyPathElementsSize; $j++) {
                        $currentCategotyPath .= "/" . $currentCategotyPathElements[$j]["Category"]["url_title"];
                    }

                    if (trim($this->params["url"]["url"], "/") == trim($currentCategotyPath, "/")) {
                        $theCategory = $foundCategoriesByURLTitle[$i];
                        break;
                    }
                }
            } else {
                if (!empty($foundCategoriesByURLTitle)) {
                    $theCategory = $foundCategoriesByURLTitle[0];
                }
            }

            if (!empty($theCategory)) {
                $theCategory["CATEGORY_PATH"] = $this->Category->getpath($theCategory["Category"]["id"]);

                $theCategory["CATEGORY_PATH_URL"] = '';
                $curegoryPathURL = '';
                for ($i = 0, $curegoryPathSize = count($theCategory["CATEGORY_PATH"]); $i < $curegoryPathSize; $i++) {
                    if ($i != 0) {
                        $curegoryPathURL .= '/';
                    } else {
                        $curegoryPathURL .= '';
                    }

                    $curegoryPathURL .= $theCategory["CATEGORY_PATH"][$i]["Category"]["url_title"];

                    $theCategory["CATEGORY_PATH_URL"][] = $curegoryPathURL;
                }


                $theCategory["CATEGORY_CHILDRENS_DIRECT"] = $this->Category->children($theCategory["Category"]["id"], true);
                $theCategory["CATEGORY_CHILDRENS_ALL"] = $this->Category->children($theCategory["Category"]["id"]);
            }
        }

        return $theCategory;
    }
function updateLocationLatlng($limit){
    
    $l = $this->Location->find('all', array('conditions' => array( 'OR' => array( 'Location.latlng =' => '','Location.latlng = '=>',')),'limit'=>$limit));
        $updates=0;
        foreach ($l as $Location) {
            if($updates<$limit){
                $geocodeResult = $this->getLatLong($Location['Location']['name']);
                if (!$this->Location->getDataSource()->isConnected()) {
                    $this->Location->getDataSource()->reconnect();
                }
                $q = "UPDATE Locations SET latlng = '" . $geocodeResult . "' where id='" . $Location['Location']['id'] . "'";
                //$this->Location->query($q);
                $updates++;
                   flush();
                ob_flush();
           
            if($this->Location->query($q)){
              echo $updates." Update Location - ". $Location['Location']['name'] . "  latlng = " . $geocodeResult . "<br/>";
            }
            
            }
        }
    
}
    function __display_request($data = array()) {


        if (!empty($data)) {

            $pass = $this->params['pass'];

            $location_id = 0;
            $category_id = 0;

            $navigationBreadcrumbData = $this->buildCrumbs($pass, $location_id, $category_id);
            $this->set(compact('navigationBreadcrumbData'));


            $this->Location->recursive = -1;
            $this->Category->recursive = -1;
            $this->Listing->recursive = -1;

            if ("LISTING_TYPE" == $data[0]) {
                $rev_count = $this->__countReviewByListing($data[1]["Listing"]["id"]);
                $main_loc_name = $this->Location->read(null, $data[1]["Location"]["parent_id"]);
                $main_loc_name = $main_loc_name['Location']['name'];
                //================================ meta_info_for_layout
                $meta_info_for_layout['head_title'] = '' . $data[1]["Listing"]["title"] . ' in ' . $data[1]["Location"]["name"] . ',' . $main_loc_name;
                $meta_info_for_layout['meta_description'] = " We have " . $rev_count . " Reviews for company " . $data[1]["Listing"]["title"] . " listed on category " . $data[1]["Category"]["name"] . " in location " . $data[1]["Location"]["name"] . "," . $main_loc_name;
                $meta_info_for_layout['meta_keywords'] = $data[1]["Listing"]["title"];
                $meta_info_for_layout['canonical'] = SITE_WEBROOT . $data[1]["Listing"]["url_title"] . '-l' . $data[1]["Listing"]["id"] . '.html';

                $this->set('meta_info_for_layout', $meta_info_for_layout);
                //===============================/ meta_info_for_layout

                $navigationBreadcrumbData = $this->buildBreadcrumbForListing($data[1]['Listing']['id']);

                $this->set(compact('navigationBreadcrumbData'));

                /*
                  //================================ navigationBreadcrumbData
                  $navigationBreadcrumbData[] = array(
                  SITE_WEBROOT.$data[1]["Location"]["url_title"],
                  $data[1]["Location"]["name"]
                  );

                  $curegoryPathURL = '';
                  for($i = 0, $curegoryPathSize = count($data[1]["CATEGORY_PATH"]); $i < $curegoryPathSize; $i++) {
                  if($i != 0) {
                  $curegoryPathURL .= '/';
                  } else {
                  $curegoryPathURL .= '';
                  }

                  $curegoryPathURL .= $data[1]["CATEGORY_PATH"][$i]["Category"]["url_title"];

                  $navigationBreadcrumbData[] = array(
                  SITE_WEBROOT.$data[1]["Location"]["url_title"].'/'.$curegoryPathURL,
                  $data[1]["CATEGORY_PATH"][$i]["Category"]["name"]
                  );
                  }

                  $navigationBreadcrumbData[] = array(
                  "",
                  "View Listing"
                  );

                  $this->set('navigationBreadcrumbData', $navigationBreadcrumbData);
                  //===============================/ navigationBreadcrumbData
                 */

                $this->set('data', $data);
                $this->set('listing', $data[1]);

                return;
            } else if ("LOCATION_TYPE" == $data[0]) {

                $limit = 10;
                if (isset($this->params['named']['ind']) && !empty($this->params['named']['ind'])) {
                    $limit = 100;
                }

                
                    $locid=$data[1]["Location"]["id"];
                    $childlocs=$this->getChildLocations($data[1]["Location"]["id"]);
                
                $sf = 3.14159 / 180;

                $locationlatlng = $this->Location->find('first', array('conditions' => array('Location.id' => $data[1]["Location"]["id"])));
                $latlng = $locationlatlng['Location']['latlng'];
                if ($latlng == '' || $latlng == ',') {
                    $latlng = $this->getLatLong($locationlatlng['Location']['name']);
                    if (!$this->Location->getDataSource()->isConnected()) {
                        $this->Location->getDataSource()->reconnect();
                    }
                    $q = "UPDATE Locations SET latlng = '" . $latlng . "' where id='" . $data[1]["Location"]["id"] . "'";
                    $this->Location->query($q);
                }
               

                $temp = split(',', $latlng);
                $lat = $temp[0];
                $lon = $temp[1];
                $sort = "ACOS(SIN(left(latlng,3)*$sf)*SIN($lat*$sf) + COS(left(latlng,3)*$sf)*COS($lat*$sf)*COS((right(latlng,3)-($lon))*$sf))";
                $this->paginate = array('Listing' => array(
                        'conditions' => array(
                            'Listing.is_active' => 1,
                            'Listing.location_id' => $childlocs
                        ),
                        'order' => array($sort => 'asc'),
                        'limit' => 10
                ));


//This code is for the meta tags
                $listing_for_meta = $this->paginate('Listing');
                $listing_for_meta_count = $this->Listing->find('count', array('conditions' => array(
                        'Listing.is_active' => 1,
                        'Listing.location_id' => $this->getChildLocations($data[1]["Location"]["id"])
                )));
                $listing_for_meta_3 = array();
                $listing_for_meta_i = 0;
                foreach ($listing_for_meta as $key => $value) {
                    if ($listing_for_meta_i < 3) {
                        $listing_for_meta_3[] = $value['Listing']['title'];
                    }
                    $listing_for_meta_i++;
                }
                $listing_for_meta_3 = implode(',', $listing_for_meta_3);




                //================================ meta_info_for_layout
                $meta_info_for_layout['head_title'] = 'Business Directory ' . $data[1]["Location"]["name"];

                $listingsCountInLocation = $this->Location->query("SELECT COUNT(1) AS `count` FROM `listings` WHERE `is_active`='1' AND `location_id` = '" . mysql_real_escape_string($locid) . "'");
                $meta_info_for_layout['meta_description'] = 'Example meta data ' . $data[1]["Location"]["name"] . ' .We have  ' . $listing_for_meta_count . ' companies listed in  ' . $data[1]["Location"]["name"] . ',including ' . $listing_for_meta_3;
                $meta_info_for_layout['meta_keywords'] = 'companies in  ' . $data[1]["Location"]["name"];

                if ((!empty($this->params["named"]["page"])) && ($this->params["named"]["page"] != 1)) {
                    $meta_info_for_layout['canonical'] = SITE_WEBROOT . $data[1]["Location"]["url_title"] . '/page:' . $this->params["named"]["page"];
                } else {
                    $meta_info_for_layout['canonical'] = SITE_WEBROOT . $data[1]["Location"]["url_title"];
                }

                $this->set('meta_info_for_layout', $meta_info_for_layout);
                //===============================/ meta_info_for_layout
                //================================ navigationBreadcrumbData
                /* 	tuannguyen2042 hide
                  $navigationBreadcrumbData = array(
                  array(
                  "",
                  $data[1]["Location"]["name"]
                  )
                  );
                  $navigationBreadcrumbData = $this->buildLocationBreadcrumbData($data[1]['Location']['id']);

                  $this->set('navigationBreadcrumbData', $navigationBreadcrumbData);
                  //===============================/ navigationBreadcrumbData
                 */

               

                $lIDSS = '';
                if ($data[1]["Location"]['parent_id'] != 0) {

                    $lIDSS = implode(',', $this->getChildLocations($data[1]["Location"]["parent_id"]));
                } else {

                    $lIDSS = implode(',', $this->getChildLocations($data[1]["Location"]["id"]));
                }
                // query all distinct first letters used in names
                $letters_p = $this->Listing->query("SELECT DISTINCT SUBSTRING(`title`, 1, 1) FROM `listings` WHERE `is_active` = 1 AND `location_id` IN (" . $lIDSS . ") ORDER BY `title`");

                $links_p = array();
                // push all letters into a non-nested array
                foreach ($letters_p as $row) {
                    array_push($links_p, current($row[0]));
                }

                $this->set('links_p', $links_p);
                //pr($this->getChildLocations($data[1]["Location"]["id"]));die;
                /*
                  $haveListingCategories = $this->Listing->find('list', array(
                  'recursive' => 0,
                  'fields' => array('Listing.category_id'),
                  'conditions' => array('Listing.is_active' => 1)
                  ));

                  $category_ids = array();
                  foreach($haveListingCategories as $k => $v){
                  $category_ids[] = $this->getRootParentCategory($v);
                  }

                  $categories = $this->Category->find('all', array(
                  'recursive' => 0,
                  'conditions' => array('Category.id' => $category_ids, 'Category.is_active' => 1),
                  'order' => 'Category.name ASC'
                  ));

                  $locations = $this->Location->find('all', array(
                  'recursive' => 0,
                  'conditions' => array('Location.parent_id' => $data[1]['Location']['id'], 'Location.is_showempty' => 1, 'Location.is_active' => 1),
                  'order' => 'Location.name ASC'
                  ));
                 */

                $categories = $this->getNotEmptySubCategories(0, $data[1]['Location']['id']);
                $locations = $this->getNotEmptySubLocations($data[1]['Location']['id']);
                $locationPath = $this->getLocationPathUrl($data[1]['Location']['id']);

                $this->set(compact('locations', 'locationPath'));
                $this->set('allCategories', $categories);

                $listing_count = $this->Listing->find('count', array('conditions' => array('Listing.is_active' => 1, 'Listing.location_id' => $this->getChildLocations($data[1]["Location"]["id"]))));
                if ($listing_count == 0 && empty($locations) && empty($categories)) {
                    $this->cakeError('error404');
                    return;
                }

                /*
                  $allCategories = $this->Category->find('all', array(
                  'conditions' => array(
                  'Category.parent_id' => 0
                  ),
                  'order' => array(
                  'Category.name' => 'ASC'
                  )
                  ));
                  //================================ remove empty categories
                  if ($data[1]["Location"]["is_showempty"] != 1) {
                  // check original location for listings
                  $foundListingInMainCategory = false; // init flag that indicates if there are listings in main category
                  $isAnyListings = $this->Category->query("SELECT count(1) AS `counter` FROM `listings` WHERE `location_id` = '".mysql_real_escape_string($data[1]["Location"]["id"])."' AND `is_active` = 1", false);
                  if ($isAnyListings[0][0]["counter"] > 0) {
                  $foundListingInMainCategory = true;
                  }

                  $categoriesWithListings = array();
                  if (!empty($allCategories)) {
                  for ($i = 0, $categoriesSize = count($allCategories); $i < $categoriesSize; $i++) {
                  // init flag that indicates if there are listings
                  $foundListingInCategory = false;

                  // first check current category for listings
                  $isAnyListings = $this->Category->query("SELECT count(1) AS `counter` FROM `listings` WHERE `category_id` = '".mysql_real_escape_string($allCategories[$i]["Category"]["id"])."' AND `location_id` = '".mysql_real_escape_string($data[1]["Location"]["id"])."' AND `is_active` = 1", false);
                  if ($isAnyListings[0][0]["counter"] > 0) {
                  $foundListingInCategory = true;
                  }

                  // second check current category's childrens
                  if (!$foundListingInCategory) {
                  $allChildrens = $this->Category->children($allCategories[$i]["Category"]["id"], false);
                  foreach ($allChildrens as $currentChildren) {
                  if (!$foundListingInCategory) {
                  $isAnyListings = $this->Category->query("SELECT count(1) AS `counter` FROM `listings` WHERE `category_id` = '".mysql_real_escape_string($currentChildren["Category"]["id"])."' AND `location_id` = '".mysql_real_escape_string($data[1]["Location"]["id"])."' AND `is_active` = 1", false);
                  if ($isAnyListings[0][0]["counter"] > 0) {
                  $foundListingInCategory = true;
                  break;
                  }
                  }
                  }
                  }

                  // add category if there are any listings in it
                  if ($foundListingInCategory) {
                  $categoriesWithListings[] = $allCategories[$i];
                  }
                  }
                  }
                  unset($allCategories);
                  $allCategories = $categoriesWithListings;
                  if ((!$foundListingInMainCategory) && empty($allCategories)) {
                  $this->cakeError('error404');
                  return;
                  }
                  }

                  //===============================/ remove empty categories

                  $this->set('allCategories', $categories);
                 */
            }
            else if ("LOCATIONS_TYPE" == $data[0]) {

                $limit = 10;
                if (isset($this->params['named']['ind']) && !empty($this->params['named']['ind'])) {
                    $limit = 100;
                }

                    $locid=array();
                    for($i=0;$i<count($data[1]);$i++){
                        $locid[]=$this->checkLocation($data[1][$i]);
                    }
                    $childlocs=$locid;                   

                $sf = 3.14159 / 180;

                $locationlatlng = $this->Location->find('first', array('conditions' => array('Location.id' => $this->checkLocation($this->params['pass'][0]))));
                $latlng = $locationlatlng['Location']['latlng'];
                $temp = split(',', $latlng);
                $lat = $temp[0];
                $lon = $temp[1];
                $sort = "ACOS(SIN(left(latlng,3)*$sf)*SIN($lat*$sf) + COS(left(latlng,3)*$sf)*COS($lat*$sf)*COS((right(latlng,3)-($lon))*$sf))";
                $this->paginate = array('Listing' => array(
                        'conditions' => array(
                            'Listing.is_active' => 1,
                            'Listing.location_id' => $childlocs
                        ),
                        'order' => array($sort => 'asc'),
                        'limit' => 10
                ));


//This code is for the meta tags
                $listing_for_meta = $this->paginate('Listing');
                $listing_for_meta_count = $this->Listing->find('count', array('conditions' => array(
                        'Listing.is_active' => 1,
                        'Listing.location_id' => $childlocs
                )));
                $listing_for_meta_3 = array();
                $listing_for_meta_i = 0;
                foreach ($listing_for_meta as $key => $value) {
                    if ($listing_for_meta_i < 3) {
                        $listing_for_meta_3[] = $value['Listing']['title'];
                    }
                    $listing_for_meta_i++;
                }
                $listing_for_meta_3 = implode(',', $listing_for_meta_3);
                //================================ meta_info_for_layout
                $meta_info_for_layout['head_title'] = 'Business Directory ' . $pass[0];

                $listingsCountInLocation = $this->Location->query("SELECT COUNT(1) AS `count` FROM `listings` WHERE `is_active`='1' AND `location_id` = '" . mysql_real_escape_string($locid) . "'");
                $meta_info_for_layout['meta_description'] = 'Example meta data ' . $pass[0] . ' .We have  ' . $listing_for_meta_count . ' companies listed in  ' . $pass[0] . ',including ' . $listing_for_meta_3;
                $meta_info_for_layout['meta_keywords'] = 'companies in  ' . $pass[0];

                if ((!empty($this->params["named"]["page"])) && ($this->params["named"]["page"] != 1)) {
                    $meta_info_for_layout['canonical'] = SITE_WEBROOT . $pass[0] . '/page:' . $this->params["named"]["page"];
                } else {
                    $meta_info_for_layout['canonical'] = SITE_WEBROOT . $pass[0];
                }

                $this->set('meta_info_for_layout', $meta_info_for_layout);
                //===============================/ meta_info_for_layout
                //================================ navigationBreadcrumbData
               

               

                $lIDSS = '';
                if ($data[1]["Location"]['parent_id'] != 0) {

                    $lIDSS = implode(',', $this->getChildLocations($data[1]["Location"]["parent_id"]));
                } else {

                    $lIDSS = implode(',', $this->getChildLocations($data[1]["Location"]["id"]));
                }
                // query all distinct first letters used in names
                $letters_p = $this->Listing->query("SELECT DISTINCT SUBSTRING(`title`, 1, 1) FROM `listings` WHERE `is_active` = 1 AND `location_id` IN (" . $lIDSS . ") ORDER BY `title`");

                $links_p = array();
                // push all letters into a non-nested array
                foreach ($letters_p as $row) {
                    array_push($links_p, current($row[0]));
                }

                $this->set('links_p', $links_p);
                //pr($this->getChildLocations($data[1]["Location"]["id"]));die;
              

                $categories = $this->getNotEmptySubCategories(0, $data[1]['Location']['id']);
                $locations = $this->getNotEmptySubLocations($data[1]['Location']['id']);
                $locationPath = $this->getLocationPathUrl($data[1]['Location']['id']);

                $this->set(compact('locations', 'locationPath'));
                $this->set('allCategories', $categories);

                $listing_count = $this->Listing->find('count', array('conditions' => array('Listing.is_active' => 1, 'Listing.location_id' => $this->getChildLocations($data[1]["Location"]["id"]))));
                if ($listing_count == 0 && empty($locations) && empty($categories)) {
                    $this->cakeError('error404');
                    return;
                }

               
            }else if ("CATEGORYLOCATION_TYPE" == $data[0]) {

                $locations = array();
                $categories = $this->getNotEmptySubCategories($data[1]['Category']['id'], $data[2]['Location']['id']);
                //if (empty($categories))
                  $locations = $this->getNotEmptySubLocations($data[2]['Location']['id']);

                $locationPath = $this->getLocationPathUrl($data[2]['Location']['id']);

                $this->set(compact('locations', 'locationPath'));
                $this->set('allCategories', $categories);


                $arrayInCategories = array();
                $sqlInCategories = '(';
                $arrayInCategories[] = $data[1]["Category"]["id"];
                $sqlInCategories .= $data[1]["Category"]["id"];
                for ($i = 0, $allChildrensSize = count($data[1]["CATEGORY_CHILDRENS_ALL"]); $i < $allChildrensSize; $i++) {
                    $arrayInCategories[] = $data[1]["CATEGORY_CHILDRENS_ALL"][$i]["Category"]["id"];
                    $sqlInCategories .= ', ' . $data[1]["CATEGORY_CHILDRENS_ALL"][$i]["Category"]["id"];
                }
                $sqlInCategories .= ')';

//pagination rule
                $sf = 3.14159 / 180;

                $locationlatlng = $this->Location->find('first', array('conditions' => array('Location.id' => $data[2]["Location"]["id"])));
                $latlng = $locationlatlng['Location']['latlng'];
                if ($latlng == '') {
                    $latlng = $this->getLatLong($locationlatlng['Location']['name']);
                    if (!$this->Location->getDataSource()->isConnected()) {
                        $this->Location->getDataSource()->reconnect();
                    }
                    $q = "UPDATE Locations SET latlng = '" . $latlng . "' where id='" . $data[2]["Location"]["id"] . "'";
                    $this->Location->query($q);
                }
               

                $temp = split(',', $latlng);
                $lat = $temp[0];
                $lon = $temp[1];
                $sort = "ACOS(SIN(left(latlng,3)*$sf)*SIN($lat*$sf) + COS(left(latlng,3)*$sf)*COS($lat*$sf)*COS((right(latlng,3)-($lon))*$sf))";
                
                 $this->paginate = array('Listing' => array(
                  'conditions' => array(
                  'Listing.is_active' => 1,
                  'Listing.category_id' => $arrayInCategories,
                  'Listing.location_id' => $this->getChildLocations($data[2]["Location"]["id"])
                  ),
                  'order' => array( $sort => 'ASC'),
                  'limit' => 10
                  )); 


                $all_count_cat_listing = $this->Listing->find('count', array(
                    'conditions' => array(
                        'Listing.is_active' => 1,
                        'Listing.category_id' => $arrayInCategories,
                        'Listing.location_id' => $this->getChildLocations($data[2]["Location"]["id"])
                )));

//This code is for the meta tags
                $listing_for_meta = $this->paginate('Listing');
                $listing_for_meta_count = count($listing_for_meta);
                $listing_for_meta_3 = array();
                $listing_for_meta_i = 0;
                foreach ($listing_for_meta as $key => $value) {
                    if ($listing_for_meta_i < 3) {
                        $listing_for_meta_3[] = $value['Listing']['title'];
                    }
                    $listing_for_meta_i++;
                }
                $listing_for_meta_3 = implode(',', $listing_for_meta_3);


                //-------------------------------- category body/page_title: replacement of "[!LOCATION!]" variable
                $data[1]["Category"]["body"] = str_replace("[!LOCATION!]", $data[2]["Location"]["name"], $data[1]["Category"]["body"]);
                $data[1]["Category"]["body"] = str_replace("[lisitngs 3]", $listing_for_meta_3, $data[1]["Category"]["body"]);
                $data[1]["Category"]["body"] = str_replace("[COUNT]", $all_count_cat_listing, $data[1]["Category"]["body"]);


                $data[1]["Category"]["page_title"] = str_replace("[!LOCATION!]", $data[2]["Location"]["name"], $data[1]["Category"]["page_title"]);
                //-------------------------------/ category body/page_title: replacement of "[!LOCATION!]" variable
                //================================ meta_info_for_layout
                if (!empty($data[1]["Category"]["page_title"])) {
                    $meta_info_for_layout['head_title'] = $data[1]["Category"]["page_title"];
                } else {
                    $meta_info_for_layout['head_title'] = $data[1]["Category"]["name"] . ' Ã�Â² ' . $data[2]["Location"]["name"];
                }

                // --- variant #1
                //$listingsCountInLocationAndCategory = $this->Location->query("SELECT COUNT(1) AS `count` FROM `listings` WHERE `is_active`='1' AND `category_id` IN ".mysql_real_escape_string($sqlInCategories)." AND `location_id` = '".mysql_real_escape_string($data[2]["Location"]["id"])."'");
                //$meta_info_for_layout['meta_description'] = 'Ã�Å¡Ã�Â°Ã‘â€šÃ�Â°Ã�Â»Ã�Â¾Ã�Â³ Ã‘Â�  ' . $data[1]["Category"]["name"] . '  ' . $data[2]["Location"]["name"] . '.Ã�Å¾Ã�Â½Ã�Â»Ã�Â°Ã�Â¹Ã�Â½ Ã�Â´Ã�Â¸Ã‘â‚¬Ã�ÂµÃ�ÂºÃ‘â€šÃ�Â¾Ã‘â‚¬Ã�Â¸Ã‘Â� Ã‘Â� Ã�ÂºÃ�Â¾Ã�Â½Ã‘â€šÃ�Â°Ã�ÂºÃ‘â€šÃ�Â¸ Ã�Â½Ã�Â° Ã‘â€žÃ�Â¸Ã‘â‚¬Ã�Â¼Ã�Â¸,Ã‘â€ Ã�ÂµÃ�Â½Ã�Â¸ Ã�Â¸ Ã�Â¸Ã�Â½Ã‘â€žÃ�Â¾Ã‘â‚¬Ã�Â¼Ã�Â°Ã‘â€ Ã�Â¸Ã‘Â� Ã�Â·Ã�Â° ' . $data[1]["Category"]["name"] . ' Ã�Â²Ã‘Å Ã�Â² ' . $data[2]["Location"]["name"] . '.Ã�â€�Ã�Â¾Ã�Â±Ã�Â°Ã�Â²Ã�Â¸ Ã‘Â�Ã�Â°Ã�Â¹Ã‘â€š,Ã�Â±Ã�Â¸Ã�Â·Ã�Â½Ã�ÂµÃ‘Â� Ã�Â¸Ã�Â»Ã�Â¸ Ã�Â»Ã�Â¸Ã�Â½Ã�Âº Ã�Â±Ã�ÂµÃ�Â·Ã�Â¿Ã�Â»Ã�Â°Ã‘â€šÃ�Â½Ã�Â¾.';
                // --- variant #2
                App::import('Helper', 'HtmlToText');
                $htmlToText = new HtmlToTextHelper();

                $tmpMetaDescr = str_replace('&nbsp;', ' ', $data[1]["Category"]["body"]);
                $meta_info_for_layout['meta_description'] = $htmlToText->html2text($tmpMetaDescr, 240, false);

                $meta_info_for_layout['meta_keywords'] = $data[1]["Category"]["name"] . ' Ã�Â² ' . $data[2]["Location"]["name"];

                $categoryPathURL = $data[1]["CATEGORY_PATH_URL"][(count($data[1]["CATEGORY_PATH_URL"]) - 1)];
                if ((!empty($this->params["named"]["page"])) && ($this->params["named"]["page"] != 1)) {
                    $meta_info_for_layout['canonical'] = SITE_WEBROOT . $data[2]["Location"]["url_title"] . '/' . $categoryPathURL . '/page:' . $this->params["named"]["page"];
                } else {
                    $meta_info_for_layout['canonical'] = SITE_WEBROOT . $data[2]["Location"]["url_title"] . '/' . $categoryPathURL;
                }

                $this->set('meta_info_for_layout', $meta_info_for_layout);
                //===============================/ meta_info_for_layout
                //================================ navigationBreadcrumbData
                /*  tuannguyen2042 hide
                  $navigationBreadcrumbData[] = array(
                  SITE_WEBROOT.$data[2]["Location"]["url_title"],
                  $data[2]["Location"]["name"]
                  );

                  $curegoryPathURL = '';
                  for($i = 0, $curegoryPathSize = (count($data[1]["CATEGORY_PATH"]) - 1); $i < $curegoryPathSize; $i++) {
                  if($i != 0) {
                  $curegoryPathURL .= '/';
                  } else {
                  $curegoryPathURL .= '';
                  }

                  $curegoryPathURL .= $data[1]["CATEGORY_PATH"][$i]["Category"]["url_title"];

                  $navigationBreadcrumbData[] = array(
                  SITE_WEBROOT.$data[2]["Location"]["url_title"].'/'.$curegoryPathURL,
                  $data[1]["CATEGORY_PATH"][$i]["Category"]["name"]
                  );
                  }

                  $navigationBreadcrumbData[] = array(
                  "",
                  $data[1]["Category"]["name"]
                  );

                  $this->set('navigationBreadcrumbData', $navigationBreadcrumbData);
                  //===============================/ navigationBreadcrumbData
                 */





                if ($data[2]["Location"]['parent_id'] != 0) {

                    $lIDSS = implode(',', $this->getChildLocations($data[2]["Location"]["parent_id"]));
                } else {

                    $lIDSS = implode(',', $this->getChildLocations($data[2]["Location"]["id"]));
                }


                // query all distinct first letters used in names
                $letters_p = $this->Listing->query("SELECT DISTINCT SUBSTRING(`title`, 1, 1) FROM `listings` WHERE `is_active` = 1 AND `location_id` IN (" . $lIDSS . ") ORDER BY `title`");

                $links_p = array();
                // push all letters into a non-nested array
                foreach ($letters_p as $row) {
                    array_push($links_p, current($row[0]));
                }

                $this->set('links_p', $links_p);

                /*
                  $allCategories = $data[1]["CATEGORY_CHILDRENS_DIRECT"];
                  //================================ remove empty categories
                  if ($data[2]["Location"]["is_showempty"] != 1) {
                  // check original category for listings
                  $foundListingInMainCategory = false; // init flag that indicates if there are listings in main category
                  $isAnyListings = $this->Category->query("SELECT count(1) AS `counter` FROM `listings` WHERE `category_id` = '".mysql_real_escape_string($data[1]["Category"]["id"])."' AND `location_id` = '".mysql_real_escape_string($data[2]["Location"]["id"])."' AND `is_active` = 1", false);
                  if ($isAnyListings[0][0]["counter"] > 0) {
                  $foundListingInMainCategory = true;
                  }

                  $categoriesWithListings = array();
                  if (!empty($allCategories)) {
                  for ($i = 0, $categoriesSize = count($allCategories); $i < $categoriesSize; $i++) {
                  // init flag that indicates if there are listings
                  $foundListingInCategory = false;

                  // first check current category for listings
                  $isAnyListings = $this->Category->query("SELECT count(1) AS `counter` FROM `listings` WHERE `category_id` = '".mysql_real_escape_string($allCategories[$i]["Category"]["id"])."' AND `location_id` = '".mysql_real_escape_string($data[2]["Location"]["id"])."' AND `is_active` = 1", false);
                  if ($isAnyListings[0][0]["counter"] > 0) {
                  $foundListingInCategory = true;
                  }

                  // second check current category's childrens
                  if (!$foundListingInCategory) {
                  $allChildrens = $this->Category->children($allCategories[$i]["Category"]["id"], false);
                  foreach ($allChildrens as $currentChildren) {
                  if (!$foundListingInCategory) {
                  $isAnyListings = $this->Category->query("SELECT count(1) AS `counter` FROM `listings` WHERE `category_id` = '".mysql_real_escape_string($currentChildren["Category"]["id"])."' AND `location_id` = '".mysql_real_escape_string($data[2]["Location"]["id"])."' AND `is_active` = 1", false);
                  if ($isAnyListings[0][0]["counter"] > 0) {
                  $foundListingInCategory = true;
                  break;
                  }
                  }
                  }
                  }

                  // add category if there are any listings in it
                  if ($foundListingInCategory) {
                  $categoriesWithListings[] = $allCategories[$i];
                  }
                  }
                  }

                  unset($allCategories);
                  $allCategories = $categoriesWithListings;
                  if ((!$foundListingInMainCategory) && empty($allCategories)) {
                  $this->cakeError('error404');
                  return;
                  }
                  }
                  //===============================/ remove empty categories
                  $this->set('allCategories', $allCategories);
                 */
            } else if ("CATEGORY_TYPE" == $data[0]) {
                 

      

      $haveListingLocations = $this->Listing->find('list', array(
        'recursive' => 0,
        'fields' => array('Listing.location_id'),
        'conditions' => array('Listing.is_active' => 1)
      ));            

      $location_ids = array();
     
      foreach($haveListingLocations as $k => $v){
          $temp=$this->getRootParentLocation($v);
       $location_ids[] = $temp['Location']['id'];
      }

    
      $locations = $this->Location->find('all', array(
        'recursive' => 0,
        'conditions' => array('Location.id' => $location_ids, 'Location.is_active' => 1),
        'order' => 'Location.name ASC'
      ));
      
              //  $locations = $this->getNotEmptySubLocations(0);
                $locationPath = $this->getLocationPathUrl(0);

                $this->set('locations', $locations);
                $arrayInCategories = array();
                $sqlInCategories = '(';
                $arrayInCategories[] = $data[1]["Category"]["id"];
                $sqlInCategories .= $data[1]["Category"]["id"];
                for ($i = 0, $allChildrensSize = count($data[1]["CATEGORY_CHILDRENS_ALL"]); $i < $allChildrensSize; $i++) {
                    $arrayInCategories[] = $data[1]["CATEGORY_CHILDRENS_ALL"][$i]["Category"]["id"];
                    $sqlInCategories .= ', ' . $data[1]["CATEGORY_CHILDRENS_ALL"][$i]["Category"]["id"];
                }
                $sqlInCategories .= ')';


                $this->paginate = array('Listing' => array(
                        'conditions' => array(
                            'Listing.is_active' => 1,
                            'Listing.category_id' => $arrayInCategories,
                        ),
                        'order' => array('Listing.id' => 'DESC'),
                        'limit' => 10
                ));


                $all_count_cat_listing = $this->Listing->find('count', array(
                    'conditions' => array(
                        'Listing.is_active' => 1,
                        'Listing.category_id' => $arrayInCategories,
                )));

//This code is for the meta tags
                $listing_for_meta = $this->paginate('Listing');
                $listing_for_meta_count = count($listing_for_meta);
                $listing_for_meta_3 = array();
                $listing_for_meta_i = 0;
                foreach ($listing_for_meta as $key => $value) {
                    if ($listing_for_meta_i < 3) {
                        $listing_for_meta_3[] = $value['Listing']['title'];
                    }
                    $listing_for_meta_i++;
                }
                $listing_for_meta_3 = implode(',', $listing_for_meta_3);


                //-------------------------------- category body/page_title: replacement of "[!LOCATION!]" variable
                $data[1]["Category"]["body"] = str_replace("[!LOCATION!]", "UK", $data[1]["Category"]["body"]);
                $data[1]["Category"]["body"] = str_replace("[lisitngs 3]", $listing_for_meta_3, $data[1]["Category"]["body"]);
                $data[1]["Category"]["body"] = str_replace("[COUNT]", $all_count_cat_listing, $data[1]["Category"]["body"]);


                $data[1]["Category"]["page_title"] = str_replace("[!LOCATION!]", 'UK', $data[1]["Category"]["page_title"]);
                $data[1]["Category"]["page_title"] = str_replace("[lisitngs 3]", '', $data[1]["Category"]["page_title"]);
                //-------------------------------/ category body/page_title: replacement of "[!LOCATION!]" variable
                //================================ meta_info_for_layout
                if (!empty($data[1]["Category"]["page_title"])) {
                    $meta_info_for_layout['head_title'] = $data[1]["Category"]["page_title"];
                } else {
                    $meta_info_for_layout['head_title'] = $data[1]["Category"]["name"] . ' Ã�Â² ' . $data[2]["Location"]["name"];
                }

                // --- variant #1
                //$listingsCountInLocationAndCategory = $this->Location->query("SELECT COUNT(1) AS `count` FROM `listings` WHERE `is_active`='1' AND `category_id` IN ".mysql_real_escape_string($sqlInCategories)." AND `location_id` = '".mysql_real_escape_string($data[2]["Location"]["id"])."'");
                //$meta_info_for_layout['meta_description'] = 'Ã�Å¡Ã�Â°Ã‘â€šÃ�Â°Ã�Â»Ã�Â¾Ã�Â³ Ã‘Â�  ' . $data[1]["Category"]["name"] . '  ' . $data[2]["Location"]["name"] . '.Ã�Å¾Ã�Â½Ã�Â»Ã�Â°Ã�Â¹Ã�Â½ Ã�Â´Ã�Â¸Ã‘â‚¬Ã�ÂµÃ�ÂºÃ‘â€šÃ�Â¾Ã‘â‚¬Ã�Â¸Ã‘Â� Ã‘Â� Ã�ÂºÃ�Â¾Ã�Â½Ã‘â€šÃ�Â°Ã�ÂºÃ‘â€šÃ�Â¸ Ã�Â½Ã�Â° Ã‘â€žÃ�Â¸Ã‘â‚¬Ã�Â¼Ã�Â¸,Ã‘â€ Ã�ÂµÃ�Â½Ã�Â¸ Ã�Â¸ Ã�Â¸Ã�Â½Ã‘â€žÃ�Â¾Ã‘â‚¬Ã�Â¼Ã�Â°Ã‘â€ Ã�Â¸Ã‘Â� Ã�Â·Ã�Â° ' . $data[1]["Category"]["name"] . ' Ã�Â²Ã‘Å Ã�Â² ' . $data[2]["Location"]["name"] . '.Ã�â€�Ã�Â¾Ã�Â±Ã�Â°Ã�Â²Ã�Â¸ Ã‘Â�Ã�Â°Ã�Â¹Ã‘â€š,Ã�Â±Ã�Â¸Ã�Â·Ã�Â½Ã�ÂµÃ‘Â� Ã�Â¸Ã�Â»Ã�Â¸ Ã�Â»Ã�Â¸Ã�Â½Ã�Âº Ã�Â±Ã�ÂµÃ�Â·Ã�Â¿Ã�Â»Ã�Â°Ã‘â€šÃ�Â½Ã�Â¾.';
                // --- variant #2
                App::import('Helper', 'HtmlToText');
                $htmlToText = new HtmlToTextHelper();

                $tmpMetaDescr = str_replace('&nbsp;', ' ', $data[1]["Category"]["body"]);
                $meta_info_for_layout['meta_description'] = $htmlToText->html2text($tmpMetaDescr, 240, false);

                $meta_info_for_layout['meta_keywords'] = $data[1]["Category"]["name"] . ' Ã�Â² ' . $data[2]["Location"]["name"];

                $categoryPathURL = $data[1]["CATEGORY_PATH_URL"][(count($data[1]["CATEGORY_PATH_URL"]) - 1)];
                if ((!empty($this->params["named"]["page"])) && ($this->params["named"]["page"] != 1)) {
                    $meta_info_for_layout['canonical'] = SITE_WEBROOT . $data[2]["Location"]["url_title"] . '/' . $categoryPathURL . '/page:' . $this->params["named"]["page"];
                } else {
                    $meta_info_for_layout['canonical'] = SITE_WEBROOT . $data[2]["Location"]["url_title"] . '/' . $categoryPathURL;
                }

                $this->set('meta_info_for_layout', $meta_info_for_layout);
                //===============================/ meta_info_for_layout

                /* tuannguyen2042 hide
                  //================================ navigationBreadcrumbData
                  $curegoryPathURL = '';
                  for($i = 0, $curegoryPathSize = (count($data[1]["CATEGORY_PATH"]) - 1); $i < $curegoryPathSize; $i++) {
                  if($i != 0) {
                  $curegoryPathURL .= '/';
                  } else {
                  $curegoryPathURL .= '';
                  }

                  $curegoryPathURL .= $data[1]["CATEGORY_PATH"][$i]["Category"]["url_title"];

                  $navigationBreadcrumbData[] = array(
                  SITE_WEBROOT.$curegoryPathURL,
                  $data[1]["CATEGORY_PATH"][$i]["Category"]["name"]
                  );
                  }

                  $navigationBreadcrumbData[] = array(
                  "",
                  $data[1]["Category"]["name"]
                  );

                  $this->set('navigationBreadcrumbData', $navigationBreadcrumbData);
                  //===============================/ navigationBreadcrumbData
                 */




                $idSSCAT = implode(',', $arrayInCategories);

                // query all distinct first letters used in names
                $letters_p = $this->Listing->query("SELECT DISTINCT SUBSTRING(`title`, 1, 1) FROM `listings` WHERE `is_active` = 1 AND `category_id` IN (" . $idSSCAT . ") ORDER BY `title`");

                $links_p = array();
                // push all letters into a non-nested array
                foreach ($letters_p as $row) {
                    array_push($links_p, current($row[0]));
                }

                $this->set('links_p', $links_p);

                $allCategories = $data[1]["CATEGORY_CHILDRENS_DIRECT"];
                //================================ remove empty categories
                /* $categoriesWithListings = array();
                  for ($i = 0, $categoriesSize = count($allCategories); $i < $categoriesSize; $i++) {
                  // init flag that indicates if there are listings
                  $foundListingInCategory = false;

                  // first check original category for listings
                  $isAnyListings = $this->Category->query("SELECT count(1) AS `counter` FROM `listings` WHERE `category_id` = '".mysql_real_escape_string($allCategories[$i]["Category"]["id"])."' AND `is_active` = 1", false);
                  if ($isAnyListings[0][0]["counter"] > 0) {
                  $foundListingInCategory = true;
                  }

                  // second check category childrens
                  if (!$foundListingInCategory) {
                  $allDirectChildrens = $this->Category->children($allCategories[$i]["Category"]["id"], true);
                  foreach ($allDirectChildrens as $allDirectChildren) {
                  if (!$foundListingInCategory) {
                  $isAnyListings = $this->Category->query("SELECT count(1) AS `counter` FROM `listings` WHERE `category_id` = '".mysql_real_escape_string($allDirectChildren["Category"]["id"])."' AND `is_active` = 1", false);
                  if ($isAnyListings[0][0]["counter"] > 0) {
                  $foundListingInCategory = true;
                  }
                  }
                  }
                  }

                  // add category if there are any listings in it
                  if ($foundListingInCategory) {
                  $categoriesWithListings[] = $allCategories[$i];
                  }
                  }
                  unset($allCategories);
                  $allCategories = $categoriesWithListings; */
                //===============================/ remove empty categories
                $this->set('allCategories', $allCategories);
            } else {
                $this->cakeError('error404');

                return;
            }
            if (isset($this->params['named']['ind']) && !empty($this->params['named']['ind'])) {
                $this->paginate = array('limit' => 100);
				        $listings = $this->paginate('Listing', array('Listing.title LIKE' => $this->params['named']['ind'] . '%'));
            } else {			    
                $listings = $this->paginate('Listing');
            }
            foreach ($listings as $key => $list_url) {
                $list_id = $list_url['Listing']['id'];
                $urlss = $this->buildBreadcrumbForListing($list_id);
                $listings[$key]['Listing']['all_urls'] = $urlss;
                $review_c = $this->__countReviewByListing($list_id);
                $listings[$key]['Listing']['review_c'] = $review_c;
                $rate = $this->__countReviewRateByListing($list_id);
                $listings[$key]['Listing']['review_rate'] = $rate;
                $paths = $this->Category->getPath($list_url['Listing']['category_id']);
            }
            $this->set('data', $data);
            $this->set('listings', $listings);
        } else {
            $this->cakeError('error404');

            return;
        }
    }

    private function getChildLocations($location_id) {
        $location_ids = array();
        $location_ids[] = $location_id;

        $childs = $this->Location->children($location_id);
        foreach ($childs as $child) {
            $location_ids[] = $child['Location']['id'];
        }
        return $location_ids;
    }

    private function getRootParentCategory($category_id) {
        $root = $this->Category->getPath($category_id);
        if (count($root) > 0)
            return $root[0]['Category']['id'];

        return $category_id;
    }

    private function getRootParentLocation($location_id) {
        $root = $this->Location->getPath($location_id);
        if (count($root) > 0)
            return $root[0];

        return array();
    }

    private function buildLocationBreadcrumbData($location_id) {
        $breadCrumbData = array();

        $childs = $this->Location->getPath($location_id);
        foreach ($childs as $child) {
            $breadCrumbData[] = array($child['Location']['url_title'], $child['Location']['name']);
        }

        return $breadCrumbData;
    }

    private function getLocationPathUrl($location_id) {
        $url = '';
        $paths = $this->Location->getPath($location_id);
        foreach ($paths as $path) {
            $url .= $path['Location']['url_title'] . '/';
        }
        return $url;
    }

    private function buildCrumbs($params, &$last_location_id, &$last_category_id) {
        $loc_crumbs = array();
        $cat_crumbs = array();
        $last_location_id = 0;
        $last_category_id = 0;

        foreach ($params as $param) {
            $location_id = $this->checkLocation($param);
            if ($location_id) {
                $paths = $this->Location->getPath($location_id);
                $loc_crumbs = array();
                foreach ($paths as $idx => $path) {
                    $url = $this->buildCrumbLocationUrl($paths, $idx);
                    $loc_crumbs[] = array('name' => $path['Location']['name'], 'url' => $url);
                }
                $last_location_id = $location_id;
            } else {
                $category_id = $this->checkCategory($param);
                if ($category_id) {
                    if ($last_location_id) {
                        //$location = $this->Location->find('first', array('conditions' => array('Location.id' => $last_location_id)));
                        $paths = $this->Location->getPath($last_location_id);
                        $location_path = $this->buildCrumbLocationUrl($paths, count($paths));
                    }

                    $paths = $this->Category->getPath($category_id);
                    $cat_crumbs = array();
                    foreach ($paths as $path) {
                        if ($location_path) {
                            $cat_crumbs[] = array('name' => $path['Category']['name'], 'url' => $location_path . $path['Category']['url_title']);
                        } else
                            $cat_crumbs[] = array('name' => $path['Category']['name'], 'url' => $path['Category']['url_title']);
                    }
                    $last_category_id = $category_id;
                }
            }
        }

        return array_merge($loc_crumbs, $cat_crumbs);
    }

    private function buildCrumbLocationUrl($paths, $idx) {
        $url = '';
        for ($i = 0; $i <= $idx; $i++) {
            $url .= $paths[$i]['Location']['url_title'] . '/';
        }
        return $url;
    }

    private function checkLocation($url_title) {
        $url_tile= str_replace(" ", "-", $url_title);
        $ret = $this->Location->find('first', array('fields' => 'id', 'conditions' => array('Location.url_title' => strtolower($url_title))));
        if (!$ret)
            return 0;

        return $ret['Location']['id'];
    }

    private function checkCategory($url_title) {
        $ret = $this->Category->find('first', array('fields' => 'id', 'conditions' => array('Category.url_title' => strtolower($url_title))));
        if (!$ret)
            return 0;

        return $ret['Category']['id'];
    }

    private function getNotEmptySubLocations($location_id) {
        $subLocations = array();

        $location = $this->getRootParentLocation($location_id);
        if (!empty($location)) {
            if ($location['Location']['is_showempty']) {
                $subLocations = $this->Location->find('all', array('recursive' => 0, 'conditions' => array('Location.parent_id' => $location_id, 'Location.is_active' => 1)));
            } else {   //get locations have listings
                $location_ids = $this->getChildLocations($location_id);
                $locationsHaveListings = $this->Listing->find('list', array(
                    'recursive' => 0,
                    'fields' => array('Listing.location_id'),
                    'conditions' => array('Listing.is_active' => 1, 'Listing.location_id' => $location_ids)
                ));

                $subLocations = $this->getParentLocations($locationsHaveListings, $location_id);
            }
        }
        return $subLocations;
    }

    private function getNotEmptySubCategories($category_id, $location_id = 0) {
        $data = array();

        if ($location_id > 0) {
            $location = $this->getRootParentLocation($location_id);
            if (!empty($location)) {
                if ($location['Location']['is_showempty']) {
                    $data = $this->Category->find('all', array(
                        'recursive' => 0,
                        'conditions' => array('Category.parent_id' => $category_id, 'Category.is_active' => 1),
                        'order' => 'Category.name ASC'
                    ));
                } else {
                    $categoriesHaveListings = $this->Listing->find('list', array(
                        'recursive' => 0,
                        'fields' => array('Listing.category_id'),
                        'conditions' => array('Listing.is_active' => 1, 'Listing.location_id' => $this->getChildLocations($location_id))
                    ));

                    $data = $this->getParentCategories($categoriesHaveListings, $category_id);
                }
            }
        } else {
            $categoriesHaveListings = $this->Listing->find('all', array(
                'recursive' => 0,
                'fields' => array('Listing.category_id'),
                'conditions' => array('Listing.is_active' => 1)
            ));

            $data = $this->getParentCategories($categoriesHaveListings, $category_id);
        }

        return $data;
    }

    private function getParentLocations($locations, $parent_id) {
        $data = array();

        /*
          foreach($locations as $k => $v){
          $paths = $this->Location->getPath($v);
          foreach($paths as $path){
          if ($path['Location']['parent_id'] == $parent_id){
          $data[] = $path;
          break;
          }
          }
          }
         */

        $data = $this->Location->find('all', array(
            'recursive' => 0,
            'conditions' => array('Location.parent_id' => $parent_id, 'Location.id' => $locations),
            'order' => 'Location.name ASC'
        ));

        return $data;
    }

    private function getParentCategories($categories, $parent_id) {
        $data = array();

        foreach ($categories as $k => $v) {
            $paths = $this->Category->getPath($v);
            foreach ($paths as $path) {
                if ($path['Category']['parent_id'] == $parent_id) {
                    $alreadyExist = false;
                    foreach ($data as $it) {
                        if ($it['Category']['id'] == $path['Category']['id']) {
                            $alreadyExist = true;
                            break;
                        }
                    }
                    if (!$alreadyExist)
                        $data[] = $path;
                    break;
                }
            }
        }
        return $data;
    }

    private function buildBreadcrumbForListing($listing_id) {
        $crumb = array();

        $listing = $this->Listing->find('first', array(
            'recursive' => 0,
            'conditions' => array('Listing.id' => $listing_id)
        ));

        if (!empty($listing)) {
            $location_url = '';
            $paths = $this->Location->getPath($listing['Listing']['location_id']);
            foreach ($paths as $idx => $path) {
                $location_url = $this->buildCrumbLocationUrl($paths, $idx);
                $crumb[] = array('name' => $path['Location']['name'], 'url' => $location_url);
            }

            $paths = $this->Category->getPath($listing['Listing']['category_id']);
            foreach ($paths as $idx => $path) {
                $crumb[] = array('name' => $path['Category']['name'], 'url' => $location_url . $path['Category']['url_title']);
            }
        }

        $crumb[] = array('name' => $listing['Listing']['title'], 'url' => $listing['Listing']['url_title']);   //for enable click last category
        return $crumb;
    }

    //tuannguyen2042 added 09/04/2013
    private function runYellCronjob($limit = 100000, $main_location = '') {
        $num_blocked_reqs = 30;
        $this->saveSqlToFile('yell_crawled_data.sql', '', false);

        if (empty($main_location)) {
            $locations = $this->Location->query('SELECT Location.id, Location.parent_id, Location.name FROM locations Location WHERE Location.id NOT IN
        (SELECT DISTINCT `parent_id` FROM `locations` WHERE `parent_id` IS NOT NULL)');
        } else {
            $loc = $this->Location->find('first', array('conditions' => array('Location.name LIKE' => $main_location, 'Location.parent_id' => 0), 'recursive' => 0));
            $locations = $this->Location->find('all', array('conditions' => array('Location.parent_id' => $loc['Location']['id']), 'recursive' => 0));
        }

        $categories = $this->Category->query('SELECT Category.id, Category.name FROM categories Category WHERE Category.id NOT IN
      (SELECT DISTINCT `parent_id` FROM `categories` WHERE `parent_id` IS NOT NULL)');

        $proxies = $this->loadProxies('proxies.txt');
        $proxies_count = count($proxies);

        $req_count = 0;
        $cur_proxy_id = 0;
        $cur_proxy = $proxies[0];
        $proxy = explode(':', $cur_proxy);

        $loccat = array();
        foreach ($locations as $location) {
            foreach ($categories as $category) {
                $listing_count = $this->Listing->find('count', array('conditions' => array('Listing.location_id' => $location['Location']['id'], 'Listing.category_id' => $category['Category']['id'])));
                if ($listing_count == 0) {
                    $parentLoc = $this->Location->find('first', array('conditions' => array('Location.id' => $location['Location']['parent_id'])));
                    $loccat[] = array('loc_id' => $location['Location']['id'],
                        'cat_id' => $category['Category']['id'],
                        'cat_name' => $category['Category']['name'],
                        'loc_name' => $location['Location']['name'],
                        'parent_loc_name' => $parentLoc['Location']['name']);
                }
            }
        }

        /*
          foreach($locations as $location){
          foreach($categories as $category){
         */
        foreach ($loccat as $it) {
            if ($limit > 0 && $req_count > $limit) {
                echo 'Reach to max requests limit. Exit!<br/>';
                flush();
                ob_flush();
                exit();
            }

            /*
              $listing_count = $this->Listing->find('count', array('conditions' => array('Listing.location_id' => $location['Location']['id'], 'Listing.category_id' => $category['Category']['id'])));
              if ($listing_count > 0){
              echo 'Location: '.$location['Location']['name'].' - Category: '.$category['Category']['name'].' is full. Skip it.<br/>';
              flush();
              ob_flush();
              continue;
              }
             */

            $proc_times = 0;
            do {
                if ($req_count > 0 && $req_count % $num_blocked_reqs == 0) {
                    echo 'Reach to ' . $num_blocked_reqs . ' requests per proxy. Switching to another one.<br/>';
                    flush();
                    ob_flush();

                    $changed = $this->changeProxy($proxies, $cur_proxy_id);
                    if (!$changed) {
                        echo 'There is no proxy alive. Update proxies.txt and run again. Exit!<br/>';
                        flush();
                        ob_flush();
                        exit;
                    } else {
                        $proxy = explode(':', $proxies[$cur_proxy_id]);
                        echo 'Changed to proxy ' . $proxies[$cur_proxy_id] . '<br/>';
                        flush();
                        ob_flush();
                    }
                }

                $proc_times++;
                //echo 'Processing location: '.$location['Location']['name'].' - Category: '.$category['Category']['name'].' - Time: '.$proc_times.'<br/>';
                echo 'What: ' . $it['cat_name'] . ' - Where: ' . $it['parent_loc_name'] . ',' . $it['loc_name'] . ' - Time: ' . $proc_times . '<br/>';
                flush();
                ob_flush();

                //$data = $this->getYellResults($location['Location']['name'], $category['Category']['name'], $proxy[0], $proxy[1]);
                $data = $this->getYellResults($it['parent_loc_name'], $it['loc_name'], $it['cat_name'], $proxy[0], $proxy[1]);
                if ($data === false || $data === 'error') {
                    echo 'Proxy ' . $proxy[0] . ':' . $proxy[1] . ' die or blocked.<br/>';
                    flush();
                    ob_flush();

                    $changed = $this->changeProxy($proxies, $cur_proxy_id);
                    if (!$changed) {
                        echo 'There is no proxy alive. Update proxies.txt and run again. Exit!<br/>';
                        flush();
                        ob_flush();
                        exit;
                    } else {
                        $proxy = explode(':', $proxies[$cur_proxy_id]);
                        echo 'Changed to proxy ' . $proxies[$cur_proxy_id] . '<br/>';
                        flush();
                        ob_flush();
                    }
                } elseif ($data !== 'error') {
                    //$listings = $this->parseYellHtml($data, $location['Location']['id'], $category['Category']['id']);
                    $listings = $this->parseYellHtml($data, $it['loc_id'], $it['cat_id']);
                    $this->saveYellListings($listings, 10);
                    $req_count++;
                    echo 'Saved data.<br/>';
                    flush();
                    ob_flush();
                    break;
                }
            } while ($data === false || $data === 'error');
        }
        //}
    }

    private function parseYellHtml($html, $location_id, $category_id) {
        $html = preg_replace('/\s\s+/', '', $html);

        //search for company name
        $companies = array();
        preg_match_all('/<h2\s*class="ellipsis">(.*?)<\/h2>/si', $html, $results);
        foreach ($results[0] as $it) {
            $companies[] = trim(html_entity_decode(strip_tags($it)));
        }

        //search for address
        $addrs = array();
        preg_match_all('/<a.*?itemprop="address".*?>(.*?)<\/a>/si', $html, $results);
        foreach ($results[1] as $it) {
            $addrs[] = trim(html_entity_decode(strip_tags($it)));
        }

        //search for phone number
        $phones = array();
        preg_match_all('/<div class="l-telephone\s(?:tel-single|tel-multiple)?">(.*?)<\/div>/si', $html, $results);
        foreach ($results[1] as $it) {
            $tmp = trim(html_entity_decode(strip_tags($it)));
            $phones[] = trim(str_replace('Tel:', '', $tmp));
        }

        //search for website urls
        $websites = array();
        //preg_match_all("/ href=(?:'|\")([^']+)(?:'|\")\sdata-company-item=\"visitwebsite\"/si", $html, $results);
        preg_match_all("/class=\"l-footer clearfix\".*?href=(?:'|\")([^']+)(?:'|\")\s(?:data-company-item=\"visitwebsite\")?/si", $html, $results);
        foreach ($results[1] as $it) {
            $tmp = trim(html_entity_decode(strip_tags($it)));
            if (stripos($tmp, 'http://') === 0)
                $websites[] = $tmp;
            else
                $websites[] = '';
        }

        $listings = array();
        for ($i = 0; $i < count($companies); $i++) {
            $listings[] = array('company' => $companies[$i],
                'addr' => $addrs[$i],
                'phone' => $phones[$i],
                'website' => $websites[$i],
                'location_id' => $location_id,
                'category_id' => $category_id);
        }
        return $listings;
    }

    function getLatLong($address) {
        $key = 'AIzaSyDGGZQocK5BPGpH20-46JVVMyo4zzcYhyk';
        $prepAddr = str_replace(' ', '+', $address);
        $str = 'https://maps.google.com/maps/api/geocode/json?address=' . $prepAddr . '&sensor=false';
        $geocode = file_get_contents($str);
        //$geocode = file_get_contents('http://dev.virtualearth.net/REST/v1/Locations?q=' . $prepAddr . '&key=' . $key);
        //return $geocode;
        /*     $output = json_decode($geocode);
          $rs = $output->resourceSets[0];
          $rs = $rs->resources[0];
          $rs = $rs->geocodePoints[0];
          $rs = $rs->coordinates;
          $latitude = $rs[0];
          $longitude = $rs[1]; */
        $output = json_decode($geocode);
        $rs = $output->results[0];
        $rs = $rs->geometry->location;
        $latitude = $rs->lat;
        $longitude = $rs->lng;

        return $latitude . ',' . $longitude;
    }

    private function updateLatLng($limit) {

        $l = $this->Listing->find('all', array('conditions' => array( 'OR' => array( 'Listing.latlng =' => '','Listing.latlng = '=>',')),'limit'=>$limit));
        $updates=0;
        foreach ($l as $listing) {
            if($updates<$limit){
                $geocodeResult = $this->getLatLong(str_replace("We serve","",$listing['Listing']['address_1']));
                if (!$this->Listing->getDataSource()->isConnected()) {
                    $this->Listing->getDataSource()->reconnect();
                }
                $q = "UPDATE listings SET latlng = '" . $geocodeResult . "' where id='" . $listing['Listing']['id'] . "'";
                $this->Listing->query($q);
                $updates++;
            
            }
        }
    }

      private function updateStreetView( $limit ) {
        // $this->updateLatLng($limit);
        $l = $this->Listing->find('all', array('conditions' =>  array('AND' => array ('Listing.streetview = ' => '','Listing.latlng <> '=>'')),
            'limit'=>$limit
        ));
        $updates=0;
        foreach ($l as $listing) {
            if($updates<$limit){
                $temp=split(',',$listing['Listing']['latlng']);
                
                $image=$this->getStreetViewImage($temp[0],$temp[1]);
                $width=imagesx(imagecreatefromstring(base64_decode($image)));
                //$geocodeResult = $this->geocode($listing['Listing']['address_1']);
                if (!$this->Listing->getDataSource()->isConnected()) {
                    $this->Listing->getDataSource()->reconnect();
                }
             //
                flush();
                ob_flush();
            $q = "UPDATE listings SET streetview='".$image."'  where id='" . $listing['Listing']['id'] . "'";
            //echo $q;
            if($width==300){
            if($this->Listing->query($q)){
              echo $updates." Update Listing ID - " . $listing['Listing']['id'] . " with Streetview Image<br/>"; 
              $updates++;
            }}
                
        }}
            }
    private function updateStations( $limit ) {
        // $this->updateLatLng($limit);
        $l = $this->Listing->find('all', array('conditions' =>  array('Listing.bus_stations =' => ''),
            'limit'=>$limit
        ));
        $updates=0;
        foreach ($l as $listing) {
            if($updates<$limit){
                $geocodeResult = $this->geocode(str_replace("We serve","",$listing['Listing']['address_1']));
                if (!$this->Listing->getDataSource()->isConnected()) {
                    $this->Listing->getDataSource()->reconnect();
                }
             //
                flush();
                ob_flush();
            $q = "UPDATE listings SET train_stations = '" . $geocodeResult['ts'] . "' , bus_stations='" . $geocodeResult['bs'] . "' , subway_stations='" . $geocodeResult['ss'] . "'  where id='" . $listing['Listing']['id'] . "'";
            //echo $q;
            if($this->Listing->query($q)){
              echo $updates." Update Listing ID - " . $listing['Listing']['id'] . ": <b>Train Stations</b> - " . $geocodeResult['ts'] . " <b>Bus Stations</b> - " . $geocodeResult['bs'] . " <b>Subway Stations</b> - " . $geocodeResult['ss'] . " <br/>"; 
            }
                $updates++;
        }}
            }
             private function updateNearbyStreets( $limit ) {
        // $this->updateLatLng($limit);
        $l = $this->Listing->find('all', array('conditions' => array('AND' => array ('Listing.nearbystreets = ' => '','Listing.latlng <> '=>'')),
            'limit'=>$limit
        ));
        $updates=0;
        foreach ($l as $listing) {
            if($updates<$limit){
                //$geocodeResult = $this->geocode($listing['Listing']['address_1']);
               
                 $url2='http://maps.googleapis.com/maps/api/directions/json?origin='.$listing['Listing']['latlng'].'&destination=Elgin,UK&sensor=false';
                $geocode = file_get_contents($url2);
                $output = json_decode($geocode);
                $output = $output->routes[0];

                $output = $output->legs[0];
                $output = $output->steps;
                $vicinity='';
                $old='';
                $count=0;
                for ($i = 0; $i < count($output); $i++) { 
                    if($count<3){
                    $out=$output[$i]->html_instructions;
                    $out=  split('<b>', $out);
                    $out=$out[2];
                    $out=  split('</b>', $out);
                    $out=$out[0];
                    $address=split(' ',$out);
                    if(strpos($listing['Listing']['address_1'], $address[0])===false && $old!=$out && $out!='' && $out!='left'){
                            $vicinity=$vicinity.'<br/>'.$out;
                            $old=$out;
                    }
                    else{
                            $count--;
                        }
                    $count=$count+1;
                    }
                    
                }
                if (!$this->Listing->getDataSource()->isConnected()) {
                    $this->Listing->getDataSource()->reconnect();
                }
             //
                flush();
                ob_flush();
            $q = "UPDATE listings SET nearbystreets='".addslashes($vicinity)."'  where id='" . $listing['Listing']['id'] . "'";
            //echo $q;
            if($this->Listing->query($q)){
              echo $updates." Update Listing ID - " . $listing['Listing']['id'] . ": Vicinity - ".$vicinity."</br>"; 
            }
                $updates++;
        }}
            }

    private function updateNearby($limit) {
        $this->updateLatLng($limit);
        $l = $this->Listing->find('all', 
                array('conditions' => 
                        array('AND' => array ('Listing.nearby = ' => '','Listing.latlng <> '=>''))
                , 'limit'=> $limit));
        $updates=0;
        foreach ($l as $listing) {
            if($listing['Listing']['latlng']!=','){
         $arr=array();
         
                $l2 = $this->Listing->find('all', array('conditions' => array('Listing.location_id = ' => $listing['Listing']['location_id'])));
                foreach ($l2 as $listing2) {
                    $latlong1 = split(',', $listing['Listing']['latlng']);
                    $latlong2 = split(',', $listing2['Listing']['latlng']);
                $from=$listing['Listing']['address_1'];
                $to=$listing2['Listing']['address_1'];
                $i=0;
                    $dist = $this->getDistanceFromLatLonInKm($latlong1[0], $latlong1[1], $latlong2[0], $latlong2[1]);
                    //$dist=$this->getDistance($from,$to);
                    if ($dist != 0) {
                    $id=$listing2['Listing']['id'];
                        $arr[] = array($id, $dist);
                    }
                 
                }
            $distance=array();

                foreach ($arr as $key => $row) {
                    $distance[$key] = $row[1];
                }
                array_multisort($distance, SORT_ASC, $arr);
                $len = count($distance);
                if ($len > 5) {
                    $len = 5;
                }
                $nearby = '';
                for ($i = 0; $i < $len; $i++) {
                $nearby = $nearby . $arr[$i][0] . ':'.$arr[$i][1].' - ';
                }
                if (!$this->Listing->getDataSource()->isConnected()) {
                    $this->Listing->getDataSource()->reconnect();
                }
            echo 'Update Listing ID - ' . $listing['Listing']['id'] . ': Nearby- [' . $nearby . '] : Latlng - '. $listing['Listing']['latlng'] .'<br/>';
                flush();
                ob_flush();
                $q = "UPDATE listings SET nearby = '" . $nearby . "'  where id='" . $listing['Listing']['id'] . "'";
                $this->Listing->query($q);
        
        }}
            }

    private function saveYellListings($listings, $limit = 10) {
        $data = array();
        $savedCount = 0;

        $sqlArr = array();
        $sql = 'INSERT INTO listings(is_active,location_id,category_id,title,url,url_title,mobile,address_1,train_stations,bus_stations,subway_stations) VALUES';

        foreach ($listings as $listing) {
            if ($savedCount < $limit) {
                $url = $listing['website'];
                $address = $listing['addr'];
                $mobile = $listing['phone'];

                if ($mobile == '') {
                    $mobile = '123456789' . time();
                }

                if ($address == '') {
                    $address = '123123123' . time();
                }

                if ($url == '') {
                    $url = '1231423123' . time();
                }

                /*
                  $l = $this->Listing->find('first', array('conditions' => array("OR" => array("Listing.url LIKE" => "%$url%",
                  "Listing.address_1 LIKE" => "%$address%",
                  "Listing.mobile LIKE" => "%$mobile%"))));
                  if ($l === false){
                 */
                //$geocodeResult = $this->geocode($listing['addr']);

                /* if (count($geocodeResult) > 0) {
                  $latitude = floatval($geocodeResult[0]->geometry->location->lat);
                  $longitude = floatval($geocodeResult[0]->geometry->location->lng);
                  } */

                /*
                  if (!$this->Listing->getDataSource()->isConnected()){
                  $this->Listing->getDataSource()->reconnect();
                  }

                  $data['Listing'] = array(
                  'is_active' => 1,
                  'location_id' => $listing['location_id'],
                  'category_id' => $listing['category_id'],
                  'title' => $listing['company'],
                  'url' => $listing['website'],
                  'url_title' => Inflector::slug(mb_strtolower($listing['company'], 'UTF-8'), '-'),
                  'mobile' => $listing['phone'],
                  'address_1' => $listing['addr'],
                  'train_stations' => $geocodeResult['ts'],
                  'bus_stations' => $geocodeResult['bs'],
                  'subway_stations' => $geocodeResult['ss'],
                  'description' => ''
                  );
                  $this->Listing->create();
                  $this->Listing->save($data);
                 */

                $s = '(1,%d,%d,"%s","%s","%s","%s","%s","%s","%s","%s")';
                $s = sprintf($s, $listing['location_id'], $listing['category_id'], $listing['company'], $listing['website'], Inflector::slug(mb_strtolower($listing['company'], 'UTF-8'), '-'), $listing['phone'], $listing['addr'], $geocodeResult['ts'], $geocodeResult['bs'], $geocodeResult['ss']);

                $sqlArr[] = $s;

                $savedCount++;
                //}
            }
        }

        if (count($sqlArr) > 0) {
            $sql .= implode(',', $sqlArr) . ';';
            $this->saveSqlToFile('yell_crawled_data.sql', $sql);
        }
    }

    private function saveSqlToFile($filename, $sql, $append = true) {
        //$filename = Router::url('/', true).$filename;
        if ($append) {
            $ret = file_put_contents($filename, $sql, FILE_APPEND | LOCK_EX);
            //var_dump($ret);
        } else {
            $ret = file_put_contents($filename, $sql);
            //var_dump($ret);
        }
    }

    private function loadProxies($filename) {
        $filename = Router::url('/', true) . $filename;
        $lines = file($filename, FILE_IGNORE_NEW_LINES);
        return $lines;
    }

    private function checkProxy($proxy_ip, $proxy_port) {
        $ch = curl_init("http://www.yell.com/");
        curl_setopt($ch, CURLOPT_PROXYTYPE, 'HTTP');
        curl_setopt($ch, CURLOPT_PROXYPORT, $proxy_port);
        curl_setopt($ch, CURLOPT_PROXY, $proxy_ip);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);
        curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 0);
        curl_setopt($ch, CURLOPT_TIMEOUT, 5);  //5 seconds
        curl_setopt($ch, CURLOPT_HEADER, 0);
        $html = curl_exec($ch);

        if ($html === false || !preg_match('/<input.*?name="scrambleSeed"\s*value="(.*?)"/si', $html, $m)) {
            echo 'Checking proxy ' . $proxy_ip . ':' . $proxy_port . '... die or blocked.<br/>';
            flush();
            ob_flush();
            return false;
        }

        echo 'Checking proxy ' . $proxy_ip . ':' . $proxy_port . '... still alive.<br/>';
        flush();
        ob_flush();

        return true;
    }

    private function changeProxy($proxies, &$current_id) {
        $success = false;
        $proxy_failed = 0;
        $proxies_count = count($proxies);

        do {
            $current_id++;
            if ($current_id >= $proxies_count)
                $current_id = 0;

            $proxy = explode(':', $proxies[$current_id]);
            $success = $this->checkProxy($proxy[0], $proxy[1]);
            if ($success === false)
                $proxy_failed++;
        }while (!$success && $proxy_failed < $proxies_count);

        return ($proxy_failed == $proxies_count && !$success) ? false : true;
    }

    private function getYellResults($parent_loc_name = '', $loc_name, $cat_name, $proxy_ip, $proxy_port) {
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, 'http://www.yell.com/');
        curl_setopt($ch, CURLOPT_PROXYTYPE, 'HTTP');
        curl_setopt($ch, CURLOPT_PROXYPORT, $proxy_port);
        curl_setopt($ch, CURLOPT_PROXY, $proxy_ip);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);
        curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 0);
        curl_setopt($ch, CURLOPT_TIMEOUT, 8);   //8 seconds
        curl_setopt($ch, CURLOPT_HEADER, 0);
        $html = curl_exec($ch);

        if ($html === false) {
            echo 'Proxy ' . $proxy_ip . ':' . $proxy_port . ' die or blocked.<br/>';
            flush();
            ob_flush();
            return false;
        }

        if (!preg_match('/<input.*?name="scrambleSeed"\s*value="(.*?)"/si', $html, $m)) {
            return 'error';
        }
        $scrambleSeed = $m[1];

        if (empty($parent_loc_name)) {
            $url = "http://www.yell.com/ucs/UcsSearchAction.do?" .
                    "keywords=" . urlencode($cat_name) .
                    "&location=" . urlencode($loc_name) .
                    "&scrambleSeed=" . urlencode($scrambleSeed) .
                    "&searchType=&M=&bandedclarifyResults=&ssm=1";
        } else {
            $url = "http://www.yell.com/ucs/UcsSearchAction.do?" .
                    "keywords=" . urlencode($cat_name) .
                    "&location=" . urlencode($loc_name) . ',' . urlencode($parent_loc_name) .
                    "&scrambleSeed=" . urlencode($scrambleSeed) .
                    "&searchType=&M=&bandedclarifyResults=&ssm=1";
        }

        echo $url . '<br/>';
        flush();
        ob_flush();

        curl_setopt($ch, CURLOPT_URL, $url);
        $html = curl_exec($ch);

        if ($html === false) {
            echo 'Proxy ' . $proxy_ip . ':' . $proxy_port . ' die or blocked.<br/>';
            flush();
            ob_flush();
            return false;
        }

        return $html;
    }

    function runYellDuplicateFilter() {
        /*
          $this->Listing->query('DELETE FROM listings
          USING listings, listings AS vtable
          WHERE (listings.url = vtable.url) OR (listings.address_1 = vtable.address_1) OR (listings.mobile = vtable.mobile)');
         */

        $this->Listing->query('DELETE n1 FROM listings n1, listings n2 WHERE (n1.id < n2.id) AND (LOCATE(n1.url, n2.url) > 0 OR n1.address_1 = n2.address_1 OR n1.mobile = n2.mobile)');
    }

    function deactiveall() {
        $this->Listing->query('UPDATE listings SET is_active = 0');
        echo 'All of listings are deactivated.';
    }

    function activelistings($num_listings = 50) {
        $this->Listing->query("UPDATE listings SET is_active = 1 ORDER BY id ASC LIMIT $num_listings");
        echo "The first $num_listings listings are activated.";
    }

    //end        

    //tuannguyen2042 added 02/15/2014
    //ajax get children categories
    public function ajaxGetChildCats($catId, $locId){      
      $this->autoRender = false;            
      $childCats = $this->getNotEmptySubCategories($catId, $locId); 
      $this->set(compact('childCats', 'locId')); 
      $this->render('/inits/ajaxGetChildCats', 'ajax');         
    }
    
    private function getSubCategoryIds($catIds){
      $subCatIds = array();
      
      foreach($catIds as $k => $catId){
        $subCatIds[] = $catId;
        
        $childs = $this->Category->children($catId);                        
        foreach ($childs as $child) {
          $subCatIds[] = $child['Category']['id'];
        }
      }
      return $subCatIds;
    }
    
    private function getSubLocationIds($locIds){
      $subLocIds = array();
      
      foreach($locIds as $k => $locId){
        $subLocIds[] = $locId;
      
        $childs = $this->Location->children($locId);
        foreach ($childs as $child) {
          $subLocIds[] = $child['Location']['id'];
        }
      }
      return $subLocIds;
    }
    
    public function ajaxFilterListings($page, $index = ''){
      $this->autoRender = false;
      
      $locations = array();
      $categories = array();
      
      foreach($this->params['form'] as $name => $value){
        if (strpos($name, 'cat') === 0)
          $categories[] = $value;
        elseif (strpos($name, 'loc') === 0)
          $locations[] = $value;
      }            
      
      if (count($locations) == 0){
        if (!empty($this->params['form']['currentLoc']))
          $locations[] = $this->params['form']['currentLoc'];
      }
      
      if (count($categories) == 0){
        if (isset($this->params['form']['currentCat']))
          $categories[] = $this->params['form']['currentCat'];
      }
      
      $categories = $this->getSubCategoryIds(array_unique($categories));
        if (count($categories) == 1)
          $categories = $categories[0];
      
      $locations = $this->getSubLocationIds(array_unique($locations));
        if (count($locations) == 1)
          $locations = $locations[0];
      
          
      //var_dump($locations);
      //var_dump($categories);
      //exit();
      
      
      //FOR INDEXING
      if (count($categories) > 0){        
        $listings = $this->Listing->find('all', array(
                                                  'conditions' => array(
                                                      'Listing.is_active' => 1, 
                                                      'AND' => array('Listing.location_id' => $locations, 'Listing.category_id' => $categories)                                                                                        
                                                  ),
                                                  'fields' => array('Listing.title'),                                                   
                                                  'recursive' => 0));                
      }
      else{        
        $listings = $this->Listing->find('all', array(
                                                  'conditions' => array(
                                                      'Listing.is_active' => 1, 
                                                      'Listing.location_id' => $locations,                                                                                             
                                                  ),
                                                  'fields' => array('Listing.title'),
                                                  'recursive' => 0));                    
      }
                  
      $links_p = array();
      foreach ($listings as $listing) {  
        $links_p[] = strtoupper(substr($listing['Listing']['title'], 0, 1));      
      }               
      $links_p = array_unique($links_p);             
      //END INDEXING 
                    
            
      if (count($categories) > 0){
        if ($index != ''){
          $this->paginate = array('Listing' => array(
                                    'conditions' => array(
                                      'Listing.is_active' => 1,                                                                             
                                      'Listing.title LIKE' => $index . '%',
                                      'AND' => array('Listing.location_id' => $locations, 'Listing.category_id' => $categories)
                                    ),                                                                                                   
                                    'limit' => 10));
        }
        else{
          $this->paginate = array('Listing' => array(
                                    'conditions' => array(
                                      'Listing.is_active' => 1, 
                                      'AND' => array('Listing.location_id' => $locations, 'Listing.category_id' => $categories)                                       
                                    ),                                              
                                    'page' => $page,                         
                                    'limit' => 10));
        }
      }
      else{
        if ($index != ''){
          $this->paginate = array('Listing' => array(
                                    'conditions' => array(
                                      'Listing.is_active' => 1, 
                                      'Listing.location_id' => $locations,
                                      'Listing.title LIKE' => $index . '%',                                   
                                    ),                                                                  
                                    'limit' => 10));
        }
        else{
          $this->paginate = array('Listing' => array(
                                    'conditions' => array(
                                      'Listing.is_active' => 1, 
                                      'Listing.location_id' => $locations                                   
                                    ),   
                                    'page' => $page,                                
                                    'limit' => 10));
        }
      }                  
      
      $listings = $this->paginate('Listing');
            
      foreach ($listings as $key => $list_url) {
        $list_id = $list_url['Listing']['id'];
        $urlss = $this->buildBreadcrumbForListing($list_id);
        $listings[$key]['Listing']['all_urls'] = $urlss;
        $review_c = $this->__countReviewByListing($list_id);
        $listings[$key]['Listing']['review_c'] = $review_c;
        $rate = $this->__countReviewRateByListing($list_id);
        $listings[$key]['Listing']['review_rate'] = $rate;          
      }  
                       
      
      $this->set(compact('listings', 'links_p'));            
      $this->render('/inits/ajaxFilterListings', 'ajax');
    }
    
    
    /*
    public function ajaxFilterListings($page, $index = ''){
      $this->autoRender = false;
      $locations = array();
      $categories = array();                        
                  
      foreach($this->params['form'] as $name => $value){
        if (strpos($name, 'cat') === 0)
          $categories[] = $value;
        elseif (strpos($name, 'loc') === 0)
          $locations[] = $value;
      }
      
      if (count($locations) == 0){
        if (!empty($this->params['form']['currentLoc']))
          $locations[] = $this->params['form']['currentLoc'];
      }
           
      
      $categories[] = $this->params['form']['currentCat'];      
      $categories = $this->getSubCategoryIds(array_unique($categories));
      if (count($categories) == 1)
        $categories = $categories[0];
      
      $locations = $this->getSubLocationIds(array_unique($locations));
      if (count($locations) == 1)
        $locations = $locations[0];
      
      //var_dump($categories);
      //var_dump($locations);
      
      //FOR INDEXING
      if (count($categories) > 0){        
        $listings = $this->Listing->find('all', array(
                                                  'conditions' => array(
                                                      'Listing.is_active' => 1, 
                                                      'Listing.location_id' => $locations, 
                                                      'Listing.category_id' => $categories,                                      
                                                  ),
                                                  'fields' => array('Listing.title'),
                                                  'recursive' => 0));                
      }
      else{        
        $listings = $this->Listing->find('all', array(
                                                  'conditions' => array(
                                                      'Listing.is_active' => 1, 
                                                      'Listing.location_id' => $locations,                                                                                             
                                                  ),
                                                  'fields' => array('Listing.title'),
                                                  'recursive' => 0));                    
      }
                  
      $links_p = array();
      foreach ($listings as $listing) {  
        $links_p[] = strtoupper(substr($listing['Listing']['title'], 0, 1));      
      }               
      $links_p = array_unique($links_p);             
      //END INDEXING 
                    
            
      if (count($categories) > 0){
        if ($index != ''){
          $this->paginate = array('Listing' => array(
                                    'conditions' => array(
                                      'Listing.is_active' => 1, 
                                      'Listing.location_id' => $locations, 
                                      'Listing.category_id' => $categories,
                                      'Listing.title LIKE' => $index . '%',
                                    ),                                                               
                                    'limit' => 10));
        }
        else{
          $this->paginate = array('Listing' => array(
                                    'conditions' => array(
                                      'Listing.is_active' => 1, 
                                      'Listing.location_id' => $locations, 
                                      'Listing.category_id' => $categories
                                    ),          
                                    'page' => $page,                         
                                    'limit' => 10));
        }
      }
      else{
        if ($index != ''){
          $this->paginate = array('Listing' => array(
                                    'conditions' => array(
                                      'Listing.is_active' => 1, 
                                      'Listing.location_id' => $locations,
                                      'Listing.title LIKE' => $index . '%',                                   
                                    ),                                                                  
                                    'limit' => 10));
        }
        else{
          $this->paginate = array('Listing' => array(
                                    'conditions' => array(
                                      'Listing.is_active' => 1, 
                                      'Listing.location_id' => $locations                                   
                                    ),   
                                    'page' => $page,                                
                                    'limit' => 10));
        }
      }                  
      
      $listings = $this->paginate('Listing');
            
      foreach ($listings as $key => $list_url) {
        $list_id = $list_url['Listing']['id'];
        $urlss = $this->buildBreadcrumbForListing($list_id);
        $listings[$key]['Listing']['all_urls'] = $urlss;
        $review_c = $this->__countReviewByListing($list_id);
        $listings[$key]['Listing']['review_c'] = $review_c;
        $rate = $this->__countReviewRateByListing($list_id);
        $listings[$key]['Listing']['review_rate'] = $rate;          
      }  
                       
      
      $this->set(compact('listings', 'links_p'));            
      $this->render('/inits/ajaxFilterListings', 'ajax');
    }
    */
    //end


    /* GeoCoding by Waleed */
    public function getStreetViewImage($lat,$lng){
        $url='http://maps.googleapis.com/maps/api/streetview?size=300x300&location=';
        $query = $url . $lat . ',' . $lng . '&sensor=false&key=AIzaSyAdFxNC0zLmfLU6y0rKRky5DxAV10GYtYs';
        $geocode = base64_encode(file_get_contents($query));
        return $geocode;

    }
    public function geocode($address, $parameters = array()) {

        $parameters['address'] = $address;
        $parameters['sensor'] = 'false';
        $param = '&radius=45000';
        $param='&rankby=distance';
        $param2= '&radius=45000';    
        $url = 'http://maps.googleapis.com/maps/api/geocode/json';

        $address = str_replace(' ', '+', $address);
        $response = file_get_contents($url . '?address=' . $address . '&sensor=false');
        $response = json_decode($response);

        $vicinity='';
        $lat = $response->results[0]->geometry->location->lat;
        $long = $response->results[0]->geometry->location->lng;
        $APIkey = 'AIzaSyAdFxNC0zLmfLU6y0rKRky5DxAV10GYtYs';

        $url='https://maps.googleapis.com/maps/api/place/nearbysearch/json?location=';
       
        $query = $url . $lat . ',' . $long . '&radius=500&sensor=false&key=' . $APIkey;
      /* 
         $url2='http://maps.googleapis.com/maps/api/directions/json?origin='.$lat . ',' . $long.'&destination=Elgin,UK&sensor=false';
         $geocode = file_get_contents($url2);
         $output = json_decode($geocode);
         $output = $output->routes[0];
         
         $output = $output->legs[0];
         $output = $output->steps;
         
         for ($i = 0; $i < count($output); $i++) {
             if($i<3){
             $out=$output[$i]->html_instructions;
             $out=  split('<b>', $out);
             $out=$out[2];
             $out=  split('</b>', $out);
             $out=$out[0];
             $vicinity=$vicinity.'<br/>'.$out;
             }
         }
        
       */
        
        //$image=$this->getStreetViewImage($lat,$long);
        $query = $url . $lat . ',' . $long . '&types=train_station' . $param . '&sensor=false&key=' . $APIkey;
        $geocode = file_get_contents($query);

        $ts = 0;
        $bs = 0;
        $ss = 0;
        $limit = 30;
        $output = json_decode($geocode);
        $output = $output->results;
        for ($i = 0; $i < count($output); $i++) {
            for ($j = 0; $j < count($output[$i]->types); $j++) {
                if ($output[$i]->types[$j] == 'train_station' && $ts < $limit) {
                    $dist = $this->getDistanceFromLatLonInKm($lat, $long, $output[$i]->geometry->location->lat, $output[$i]->geometry->location->lng);
                    $TS[] = array (str_replace("'", '', $output[$i]->name), $dist);
                    $ts++;
           
                }
            }
           
        }
        if($ts==0){
            $query = $url . $lat . ',' . $long . '&types=train_station' . $param2 . '&sensor=false&key=' . $APIkey;
            $geocode = file_get_contents($query);
            $output = json_decode($geocode);
            $output = $output->results;
            for ($i = 0; $i < count($output); $i++) {
                for ($j = 0; $j < count($output[$i]->types); $j++) {
                    if ($output[$i]->types[$j] == 'train_station' && $ts < $limit) {
                        $dist = $this->getDistanceFromLatLonInKm($lat, $long, $output[$i]->geometry->location->lat, $output[$i]->geometry->location->lng);
                        $TS[] = array (str_replace("'", '', $output[$i]->name), $dist);
                        $ts++;
                    }
                }
               
            }
        
        }

        foreach ($TS as $key => $row) {
            $distance[$key] = $row[1];
        }
        array_multisort($distance, SORT_ASC, $TS);
        $TrainStations='';
        $i=0;
        foreach($TS as $T) {
            if($i<3){
                $TrainStations = $TrainStations . $T[0] . ' - ' . $T[1] . ' miles <br/>';
                $i++;
            }
        }
        $final['ts'] = addslashes($TrainStations);

        $query = $url . $lat . ',' . $long . '&types=subway_station' . $param . '&sensor=false&key=' . $APIkey;
        $geocode = file_get_contents($query);
        $output = json_decode($geocode);
        $output = $output->results;
        for ($i = 0; $i < count($output); $i++) {
            for ($j = 0; $j < count($output[$i]->types); $j++) {
                if ($output[$i]->types[$j] == 'subway_station' && $ss < $limit) {
                    $dist = $this->getDistanceFromLatLonInKm($lat, $long, $output[$i]->geometry->location->lat, $output[$i]->geometry->location->lng);
                    $SS[] = array(str_replace("'", '', $output[$i]->name), $dist);
                    $ss++;
                }
            }
           
        }
        if($ss==0){
            $query = $url . $lat . ',' . $long . '&types=subway_station' . $param2 . '&sensor=false&key=' . $APIkey;
            $geocode = file_get_contents($query);
            $output = json_decode($geocode);
            $output = $output->results;
            for ($i = 0; $i < count($output); $i++) {
                for ($j = 0; $j < count($output[$i]->types); $j++) {
                    if ($output[$i]->types[$j] == 'subway_station' && $ss < $limit) {
                        $dist = $this->getDistanceFromLatLonInKm($lat, $long, $output[$i]->geometry->location->lat, $output[$i]->geometry->location->lng);
                        $SS[] = array(str_replace("'", '', $output[$i]->name), $dist);
                        $ss++;
                    }
                }
            }
           
        }
        foreach ($SS as $key => $row) {
            $distance2[$key] = $row[1];
        }
        array_multisort($distance2, SORT_ASC, $SS);
        $SubWays='';
        $i=0;
        foreach($SS as $S)  {
            if($i<3){
                $SubWays = $SubWays . $S[0] . ' - ' . $S[1] . ' miles <br/>';
                $i++;
            }
        }
        $final['ss'] = addslashes($SubWays);

        $query = $url . $lat . ',' . $long . '&types=bus_station' . $param . '&sensor=false&key=' . $APIkey;
        $geocode = file_get_contents($query);
        $output = json_decode($geocode);
        $output = $output->results;
        for ($i = 0; $i < count($output); $i++) {
            for ($j = 0; $j < count($output[$i]->types); $j++) {
                if ($output[$i]->types[$j] == 'bus_station' && $bs < $limit) {
                    $dist = $this->getDistanceFromLatLonInKm($lat, $long, $output[$i]->geometry->location->lat, $output[$i]->geometry->location->lng);
                    $BS[] = array(str_replace("'", '', $output[$i]->name), $dist);
                    $bs++;
                }
            }
           
        }
        if($bs==0){
            $query = $url . $lat . ',' . $long . '&types=bus_station' . $param2 . '&sensor=false&key=' . $APIkey;
            $geocode = file_get_contents($query);
            $output = json_decode($geocode);
            $output = $output->results;
            for ($i = 0; $i < count($output); $i++) {
                for ($j = 0; $j < count($output[$i]->types); $j++) {
                    if ($output[$i]->types[$j] == 'bus_station' && $bs < $limit) {
                        $dist = $this->getDistanceFromLatLonInKm($lat, $long, $output[$i]->geometry->location->lat, $output[$i]->geometry->location->lng);
                        $BS[] = array(str_replace("'", '', $output[$i]->name), $dist);
                        $bs++;
                    }
                }
            }
           
            
        }
        foreach ($BS as $key => $row) {
            $distance3[$key] = $row[1];
        }
        array_multisort($distance3, SORT_ASC, $BS);
        $BusStops='';
        $i=0;
        foreach($BS as $B) {
            if($i<3){
                $BusStops = $BusStops . $B[0] . ' - ' . $B[1] . ' miles <br/>';
                $i++;
            }
        }
        $final['bs'] = addslashes($BusStops);
       //$final['vicinity']=addslashes($vicinity);
       //$final['image']=$image;
        flush();
        ob_flush();
        return $final;
    }
function getDistance($From,$Dest){

        $BingMapsKey = "AkujuXWu29G20Ybp3z_cak9XBoDObDpEXtjqZdtmSS - Egk00341drs5EfO8d8B98";
        //  $From=$lat1.','.$lon1;
        // $Dest=$lat2.','.$lon2;
        $From = str_replace(' ', '+', $From);
$Dest =str_replace(' ', '+', $Dest);
    $str = "http://maps.googleapis.com/maps/api/distancematrix/json?origins=".$From."&destinations=".$Dest."&mode=driving&sensor=false";
        $geocode = file_get_contents($str);
        $output = json_decode($geocode);
        //echo $output;
        $output = $output->rows[0];
            $output =$output->elements[0];
            $output= $output->distance->value;
        return $output;

        //   DriveDistance = oHttpReq.responseXML.SelectNodes("//TravelDistance").Item(0).Text

 
    }
    function getDistanceFromLatLonInKm($lat1, $lon1, $lat2, $lon2) {
        $R = 6371; // Radius of the earth in km
        $dLat = $this->deg2rad($lat2 - $lat1);  // deg2rad below
        $dLon = $this->deg2rad($lon2 - $lon1);
        $a = sin($dLat / 2) * sin($dLat / 2) +
                cos($this->deg2rad($lat1)) * cos($this->deg2rad($lat2)) *
                sin($dLon / 2) * sin($dLon / 2)
        ;
        $c = 2 * atan2(sqrt($a), sqrt(1 - $a));
        $d = $R * $c; // Distance in km
        //return round($d * 100) / 100;
        return round($d * 0.621371 * 100) / 100;
    }

    function deg2rad($deg) {
        return $deg * (pi() / 180);
    }

// $id=> listing id
    private function __countReviewByListing($id) {
        return $this->Review->find('count', array('conditions' => array('listing_id' => $id, 'status' => 1)));
    }

// $id=> listing id
    private function __countReviewRateByListing($id) {
        $reviews = $this->Review->find('all', array('fields' => array('AVG(Review.rate) AS AvgRate'), 'conditions' => array('listing_id' => $id, 'status' => 1)));

        return $reviews[0][0]['AvgRate'];
    }

}
?>